In MySQL 4.1 database I have the following table:
CREATE TABLE name_index ( id int(10) unsigned NOT NULL auto_increment, name varchar(200) NOT NULL default ‘’, PRIMARY KEY (id), KEY name (name))
It includes about 800.000 names ordered by importance. If I run the following query:
SELECT * FROM name_index WHERE name LIKE ‘sai%’ LIMIT 10
it returns 10 rows starting with “sai” but not in the same order like they are stored in the table.
What I need is first 10 rows starting with “sai”.
If I run this query:
SELECT * FROM name_index WHERE name LIKE ‘sai%’ ORDER BY id LIMIT 10
it works but it is slow because MySQL uses filesort.
Do you have any idea how to solve this? Any help is very appreciated.