Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
The answer is it depends, Frankly, according to your results, I would rather answer the difference is marginal. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337070 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
Rex, wonderful results. Thanks. On Tue, Sep 14, 2010 at 9:48 PM, rex li...@pgrworld.com wrote: Over 100,000 queries this is what I got (in ms): LIKELEFT 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.401,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.601,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! Won Lee wrote: I ran a quick test CREATE TABLE HoF ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, LastName VARCHAR(100) ) ENGINE = InnoDB; ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337071 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
Over 100,000 queries this is what I got (in ms): LIKELEFT 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.401,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.601,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! Won Lee wrote: I ran a quick test CREATE TABLE HoF ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, LastName VARCHAR(100) ) ENGINE = InnoDB; ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337034 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
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. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337036 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
Any advantage to one over the other? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336902 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
With SQL Server, DEFINITELY go with left(str, 4) = 'string' It has much less processing overhead. -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Wednesday, September 08, 2010 1:20 PM To: cf-talk Subject: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%' Any advantage to one over the other? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336903 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
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) sd1...@att.com wrote: With SQL Server, DEFINITELY go with left(str, 4) = 'string' It has much less processing overhead. -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Wednesday, September 08, 2010 1:20 PM To: cf-talk Subject: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%' Any advantage to one over the other? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336904 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
On Wed, Sep 8, 2010 at 1:27 PM, Michael Grant mgr...@modus.bz wrote: 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 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336907 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
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, Won Lee won...@gmail.com wrote: On Wed, Sep 8, 2010 at 1:27 PM, Michael Grant mgr...@modus.bz wrote: tioin 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 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336909 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
Great suggestion. Thanks. On Wed, Sep 8, 2010 at 2:26 PM, Russ Michaels r...@michaels.me.uk wrote: 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, Won Lee won...@gmail.com wrote: On Wed, Sep 8, 2010 at 1:27 PM, Michael Grant mgr...@modus.bz wrote: tioin 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 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336911 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
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 On Wed, Sep 8, 2010 at 1:27 PM, Michael Grant mgr...@modus.bz wrote: 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) sd1...@att.com wrote: With SQL Server, DEFINITELY go with left(str, 4) = 'string' It has much less processing overhead. -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Wednesday, September 08, 2010 1:20 PM To: cf-talk Subject: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%' Any advantage to one over the other? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336913 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
Hmmm. That seems to conflict with what Steven says. Perhaps a blood match is in order? On Wed, Sep 8, 2010 at 3:10 PM, Mike Chabot mcha...@gmail.com wrote: 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 On Wed, Sep 8, 2010 at 1:27 PM, Michael Grant mgr...@modus.bz wrote: 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) sd1...@att.com wrote: With SQL Server, DEFINITELY go with left(str, 4) = 'string' It has much less processing overhead. -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Wednesday, September 08, 2010 1:20 PM To: cf-talk Subject: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%' Any advantage to one over the other? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336914 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
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 -Original Message- From: Mike Chabot [mailto:mcha...@gmail.com] Sent: Wednesday, September 08, 2010 3:10 PM To: cf-talk Subject: Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%' 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 On Wed, Sep 8, 2010 at 1:27 PM, Michael Grant mgr...@modus.bz wrote: 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) sd1...@att.com wrote: With SQL Server, DEFINITELY go with left(str, 4) = 'string' It has much less processing overhead. -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Wednesday, September 08, 2010 1:20 PM To: cf-talk Subject: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%' Any advantage to one over the other? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336915 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
Or, if anyone really cares, just write both the queries, fire up the Query Profiler (for MSSQL) and see what the execution plans say. On Wed, Sep 8, 2010 at 12:12 PM, Michael Grant mgr...@modus.bz wrote: Hmmm. That seems to conflict with what Steven says. Perhaps a blood match is in order? On Wed, Sep 8, 2010 at 3:10 PM, Mike Chabot mcha...@gmail.com wrote: 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 On Wed, Sep 8, 2010 at 1:27 PM, Michael Grant mgr...@modus.bz wrote: 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) sd1...@att.com wrote: With SQL Server, DEFINITELY go with left(str, 4) = 'string' It has much less processing overhead. -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Wednesday, September 08, 2010 1:20 PM To: cf-talk Subject: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%' Any advantage to one over the other? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336916 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
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'. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336918 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
Yes I did. Apparently I can't count. :D On Wed, Sep 8, 2010 at 3:45 PM, Won Lee won...@gmail.com wrote: 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'. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336919 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
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, Won Lee won...@gmail.com wrote: 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'. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336923 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
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
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336927
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
