Order of results from WHERE LIKE

[thomas88]

Hello,

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.

Thomas

[VictorZ]

Two suggestions:

1. Do a “select [columns]” instead of “select *”

2. Make the “name” field a full-text field, and avoid using LIKE

Hope it helps!

[thomas88]

Thanks, but it doesn’t solve the problem.
I need the results orderd by id, but when I add “ORDER BY id” in the query, mysql uses filesort which is very slow.
Any other idea?

[VictorZ]

See if a “order by null” helps.

[thomas88]

Unfortunately not. It has the same effect like the query without ORDER BY.

[sterin]

What you want is:

ALTER TABLE name_index ADD INDEX name_index_ix_name_id (name, id);

That will create a combined index for you.

Which combined with your:

SELECT name, id FROM name_index WHERE name LIKE ‘sai%’ ORDER BY id LIMIT 10

should be fast enough for your needs.

The trick is that the combined index has all the where columns in the index and the order by column is last.
That gives mysql the chance to use the index to solve both the where part and the order by.

[thomas88]

Thank you very much for your help! The explain-extra now reads “Using where; Using index; Using filesort” and the query really runs a little faster.