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!
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
