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
