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): > 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:337071 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
