Best way to select records by what they begin with? 2

[BillyRayPreachersSon]

Hi,

I want to select some records based upon the first 3 characters of one of their fields.

All of the following commands work perfectly for me, and on my small local dataset (10 or so records out of 40), there's no noticeable difference in speed:

SELECT * FROM Page WHERE name LIKE 'bus%';

SELECT * FROM Page WHERE name REGEXP '^bus';

SELECT * FROM Page WHERE LEFT(name, 3)='bus';

SELECT * FROM Page WHERE INSTR(name, 'bus')=1;

SELECT * FROM Page WHERE LOCATE('bus', name)=1;

SELECT * FROM Page WHERE SUBSTRING(name, 1, 3)='bus';

What I want to know is, which method would be better when scaled up to a larger number of records (perhaps 1000)?

My hunch is that INSTR would be faster than LIKE or REGEXP, but not sure about the other string ones.

Any advice?

Thanks!

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Snippets & Info:

http://www.codecouch.com/

The Out Atheism Campaign

 

[jaxtell]

I just tried each of these against a MySQL 4.1.20 database on a table with 32k records. For me, LIKE was the fastest at .033 seconds, followed by LEFT, then SUBSTRING. The slowest, REGEXP, took .067 seconds.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[KarveR]

A table with 1.77 million records returns all results in a maximum of 0.02 seconds, the field is non indexed.

The server is quad core xeon with 8gb of ram all laid over to mysql.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

scaled up, INSTR and LOCATE will be the slowest

i believe LEFT will utilize an index (and thus be as fast as LIKE without the leading wildcard), and SUBSTRING starting at position 1 might also

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[BillyRayPreachersSon]

Great - thanks very much for this. I'll switch my SQL to use 'like' instead

Dan



Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page [blue]@[/blue] Code Couch:

http://www.codecouch.com/dan/

Code Couch Snippets & Info:

http://www.codecouch.com/

The Out Atheism Campaign