# MySQL: Avoid filesort when using ORDER BY LIMIT

**URL:** <https://forums.percona.com/t/mysql-avoid-filesort-when-using-order-by-limit/4156>\
**Category:** Other MySQL® Questions\
**Created:** [April 14, 2015, 10:23am UTC](https://forums.percona.com/t/mysql-avoid-filesort-when-using-order-by-limit/4156 "2015-04-14T10:23:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![infdude666](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/infdude666/32/11425_2.png) [@infdude666](https://forums.percona.com/u/infdude666)\
**Post date:** [April 14, 2015, 10:23am UTC](https://forums.percona.com/t/mysql-avoid-filesort-when-using-order-by-limit/4156/1 "2015-04-14T10:23:32Z")

</div>

```auto

CREATE TABLE table1 ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `kid` int(10) unsigned NOT NULL, `table_group` varchar(100) COLLATE utf8_bin NOT NULL) ENGINE=InnoDB; 

```

I Got index on following Column;

```auto

PRIMARY KEY (`id`) KEY `index1` (`kid`,`table_group`); 

```

The table has 5million rows matching to the where clause; When i do a explain on the below query its doing filesort and the runtime with limit its take 10seconds which is very high.

```auto

mysql> explain select * from db1.table1 FORCE INDEX(index1) where kid=187 and table_group in ('NOT_PRESENT', 'NOTHING', 'PERROR') order by id limit 200\G ***************************1. row*************************** id: 1 select_type: SIMPLE table: tabl1 type: range possible_keys: index1 key: index1 key_len: 306 ref: NULL rows: 1052764 Extra: Using index condition; Using filesort 

```

I want to avoid this filesort; Please help

---

<div class="post-metadata">

**Author:** ![wagnerbianchi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/wagnerbianchi/32/802_2.png) [@wagnerbianchi](https://forums.percona.com/u/wagnerbianchi)\
**Post date:** [April 14, 2015, 2:24pm UTC](https://forums.percona.com/t/mysql-avoid-filesort-when-using-order-by-limit/4156/2 "2015-04-14T14:24:38Z")

</div>

LIMIT won’t make your query to not read all the table’s data even having a feeling that you’re limiting the results by 100 rows of thousands (e.g. SELECT \* FROM LIMIT 100). Considering that table’s size you’re reporting, we need to have an eyes on your query and give you additional advice about how to avoid the data classification or filesort. As the index condition is appearing as an extra details on the EXPLAIN results, I presume that the query is using something like kid=? and table\_group=‘?’…can you share the query?

Cheers.

---

<div class="post-metadata">

**Author:** ![infdude666](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/infdude666/32/11425_2.png) [@infdude666](https://forums.percona.com/u/infdude666)\
**Post date:** [April 14, 2015, 11:20pm UTC](https://forums.percona.com/t/mysql-avoid-filesort-when-using-order-by-limit/4156/3 "2015-04-14T23:20:53Z")

</div>

Hi [wagnerbianchi,](http://www.percona.com/forums/member/1204-wagnerbianchi) Below is the query you asked for.

> [@](#):
>
> select \* from db1.table1 FORCE INDEX(index1) where kid=187 and table\_group in (‘NOT\_PRESENT’, ‘NOTHING’, ‘PERROR’) order by id limit 200;

---

<div class="post-metadata">

**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\
**Post date:** [April 15, 2015, 12:39am UTC](https://forums.percona.com/t/mysql-avoid-filesort-when-using-order-by-limit/4156/4 "2015-04-15T00:39:53Z")

</div>

Hi infdude666;

Try adding an index just on “kid”, and make sure to remove the index hint while testing the new index.

-Scott