# Order of results from WHERE LIKE

**URL:** <https://forums.percona.com/t/order-of-results-from-where-like/358>\
**Category:** Other MySQL® Questions\
**Created:** [June 16, 2007, 10:39am UTC](https://forums.percona.com/t/order-of-results-from-where-like/358 "2007-06-16T10:39:42Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![thomas88](https://avatars.discourse-cdn.com/v4/letter/t/919ad9/32.png) [@thomas88](https://forums.percona.com/u/thomas88)\
**Post date:** [June 16, 2007, 10:39am UTC](https://forums.percona.com/t/order-of-results-from-where-like/358/1 "2007-06-16T10:39:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![VictorZ](https://avatars.discourse-cdn.com/v4/letter/v/b3f665/32.png) [@VictorZ](https://forums.percona.com/u/VictorZ)\
**Post date:** [June 16, 2007, 10:51am UTC](https://forums.percona.com/t/order-of-results-from-where-like/358/2 "2007-06-16T10:51:59Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![thomas88](https://avatars.discourse-cdn.com/v4/letter/t/919ad9/32.png) [@thomas88](https://forums.percona.com/u/thomas88)\
**Post date:** [June 16, 2007, 11:57am UTC](https://forums.percona.com/t/order-of-results-from-where-like/358/3 "2007-06-16T11:57:00Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![VictorZ](https://avatars.discourse-cdn.com/v4/letter/v/b3f665/32.png) [@VictorZ](https://forums.percona.com/u/VictorZ)\
**Post date:** [June 16, 2007, 3:17pm UTC](https://forums.percona.com/t/order-of-results-from-where-like/358/4 "2007-06-16T15:17:36Z")

</div>

See if a “order by null” helps.

---

<div class="post-metadata">

**Author:** ![thomas88](https://avatars.discourse-cdn.com/v4/letter/t/919ad9/32.png) [@thomas88](https://forums.percona.com/u/thomas88)\
**Post date:** [June 16, 2007, 3:51pm UTC](https://forums.percona.com/t/order-of-results-from-where-like/358/5 "2007-06-16T15:51:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [June 17, 2007, 1:43pm UTC](https://forums.percona.com/t/order-of-results-from-where-like/358/6 "2007-06-17T13:43:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![thomas88](https://avatars.discourse-cdn.com/v4/letter/t/919ad9/32.png) [@thomas88](https://forums.percona.com/u/thomas88)\
**Post date:** [June 18, 2007, 11:09am UTC](https://forums.percona.com/t/order-of-results-from-where-like/358/7 "2007-06-18T11:09:13Z")

</div>

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.