WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'

Any advantage to one over the other?

With SQL Server, DEFINITELY go with left(str, 4) = 'string' It has much less processing overhead. Any advantage to one over the other?

What about mySQL? Do you know if this is documented and easy to find? On Wed, Sep 8, 2010 at 1:23 PM, DURETTE, STEVEN J (ATTASIAIT) < sd1985@att.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

> What about mySQL? > > Do you know if this is documented and easy to find? > > > http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html

turn on debug mode, run both versions and then look at the execution time of the cfquery, this will show you which processed faster. On Wed, Sep 8, 2010 at 7:11 PM, W ----- Excess quoted text cut - see Original Post for more -----

Great suggestion. Thanks. ----- Excess quoted text cut - see Original Post for more -----

In SQL Server go with "like str%." The reason is that like str% is sargable and functions are not. Functions also have overhead that native set-based SQL does not. I would assume the same is true with mySQL. Native SQL is usually faster than functions as a general rule, unless the equivalent SQL is wildly complex relative to what the function is doing for you. -Mike Chabot ----- Excess quoted text cut - see Original Post for more -----

Hmmm. That seems to conflict with what Steven says. Perhaps a blood match is in order? ----- Excess quoted text cut - see Original Post for more -----

Or, if anyone really cares, just write both the queries, fire up the Query Profiler (for MSSQL) and see what the execution plans say. ----- Excess quoted text cut - see Original Post for more -----

mike, Please let us know what you find out.  I'm very curious of this myself.  As the document clearly states, mysql will use an index when you use a like but don't start the string with a wildcard.  So we know that Left(str,5) = 'string' VS WHERE str LIKE 'string%'both will use an index. The question now becomes the cost of using LEFT vs using the LIKE. BTW, I assume you meant where left(str, 6) = 'string'.

Yes I did. Apparently I can't count. :D On Wed, Sep 8, 2010 at 3:45 PM, W ----- Excess quoted text cut - see Original Post for more -----

Getting to actually test this hasn't been as easy as I'd hoped. If I can get something definitive I'll post. On Wed, Sep 8, 2010 at 3:45 PM, W ----- Excess quoted text cut - see Original Post for more -----

I ran a quick test.... CREATE TABLE HoF (          ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,          LastName VARCHAR(100)        ) ENGINE = InnoDB; insert into HoF (LastName) values ('Smith'); insert into HoF (LastName) values ('Smithville'); insert into HoF (LastName) values ('Jones'); insert into HoF (LastName) values ('Smithy'); insert into HoF (LastName) values ('Smit'); select lastname from HoF where left(lastname, 5) = 'smith'; select lastname from HoF where lastname like 'smith%'; explain extended select lastname from HoF where left(lastname, 5) = 'smith'; explain extended select lastname from HoF where lastname like 'smith%'; create index lname_index on HoF (lastname(100)); explain extended select lastname from HoF where left(lastname, 5) = 'smith'; explain extended select lastname from HoF where lastname like 'smith%'; Conclusion, which seems pretty obvious now, is that Like is the better route.  LEFT will have to read every row so it can execute the LEFT function against it while the LIKE column will filter out columns that it doesn't meet the condition. It's really late and I had a tough day at work so please correct me if anyone sees anything wrong with my analysis. W

Over 100,000 queries this is what I got (in ms):     LIKE   LEFT MyISAM      16,215.60      16,069.00 InnoDB      16,168.60      15,987.80 MSSQL Server      28,268.60      26,775.20 Won's Test        1,184.40        1,168.80 INDEXED MyISAM      16,256.20      16,303.20 INDEXED InnoDB      16,112.40      16,119.00 INDEXED MSSQL Server      24,509.40      25,337.40 INDEXED Won's Test        1,156.60        1,159.00 LEFT() wins for unindexed columns, LIKE wins for indexed columns. The answer is "it depends", but eventually the answer becomes "it won't matter".  These days, the sql engine/optimizer is smart enough to decide how to approach the problem. "It depends" on certain things:     how wide your column is     if the column is indexed     how wide the search string is     how many records returned     how many records being searched     datatype of the column     etc. etc. Then eventually "it won't matter", since the results would only be nanoseconds apart (if you are comparing exactly the same columns).  This is over 100K queries and they're only ms apart.  I'd love to see other people's test results though! W ----- Excess quoted text cut - see Original Post for more -----

Interesting.  Thanks for the results.  There probably is another route we could test.  We could right a function in C, compile it , and add it to mysql.  I've never done it myself but I did read that it may make your query faster.  I think I also I read that it might slow it down. BTW according to the mysql explain, the real reason why the LIKE wins in this case is when you start to join other tables.  In my example the DB server will execute against every row with the LEFT statement while the execution plan for the LIKE will try execute against 40% of the rows in the db with another table.

Rex, wonderful results. Thanks. ----- Excess quoted text cut - see Original Post for more -----

  >>The answer is "it depends", Frankly, according to your results, I would rather answer "the difference is marginal".

Well, I have to apologize! I am going through some training right now that is telling me exactly that. I was always told before that using like was bad, but apparently it is better. Steve In SQL Server go with "like str%." The reason is that like str% is sargable and functions are not. Functions also have overhead that native set-based SQL does not. I would assume the same is true with mySQL. Native SQL is usually faster than functions as a general rule, unless the equivalent SQL is wildly complex relative to what the function is doing for you. -Mike Chabot ----- Excess quoted text cut - see Original Post for more -----