Both of these queries will produce same result. But which one is better, faster. <pre class="prettyprint"><code>SELECT LEFT(textField, 100) AS first100Chars SELECT SUBSTRING(textField, 1, 100 ) </code></pre> Thanks.

By itself there is little difference, however <code>left</code> can potentially utilize an index while <code>substring</code> cannot.

MySQL Left() or SUBSTRING()?

Tags:

substring

sql

mysql

Both of these queries will produce same result. But which one is better, faster.

SELECT LEFT(textField, 100) AS first100Chars

SELECT SUBSTRING(textField, 1, 100 )

Thanks.

437

asked Dec 04 '13 18:12

Pars

2 Answers

By itself there is little difference, however left can potentially utilize an index while substring cannot.

answered Sep 21 '22 12:09

Konstantin

Substring will take little more time than left function. Though your case is straight forward use left function only.

answered Sep 18 '22 12:09

Pankhuri Kaushik

Related questions
                            
                                MySQL enums empty value
                            
                                How to write LEFT OUTER JOIN on the same table in jOOQ?
                            
                                MySQL, ORDER BY insertion order, no sorting columns
                            
                                How does OPENQUERY differ for SELECT and INSERT?
                            
                                Rails: Performance issue with joining of records
                            
                                MySQL #1364 - Field 'column_name' doesn't have a default value - Can't insert into DB [duplicate]
                            
                                Selecting all corresponding fields using MAX and GROUP BY
                            
                                show create view definition
                            
                                Minimizing SQL queries using join with one-to-many relationship
                            
                                java.sql.SQLException: Incorrect string value: '\xF3\xBE\x8D\x81'
                            
                                Order By a field being equal to a specific value?
                            
                                Why won't MySQL let me remove attribute "on update CURRENT_TIMESTAMP"?
                            
                                How do I set default collation for all tables that are created in a database?
                            
                                How do I ensure I caught all errors from MySQLi::multi_query?
                            
                                How to specify Ruby regex when using Active Record in Rails?
                            
                                What is the difference between DEFAULT NULL and the CHECKBOX NULL mysql?
                            
                                SQL Query optimization to avoid temporary table
                            
                                Showing Featured Item From the Database
                            
                                HQL is generating incomplete 'cross join' on executeUpdate
                            
                                SQLAlchemy DELETE Error caused by having a both lazy-load AND a dynamic version of the same relationship