MySQL: Avoid filesort when using ORDER BY LIMIT

MySQL & MariaDB Other MySQL® Questions
1 

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;


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.


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

2

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.

3

Hi wagnerbianchi, Below is the query you asked for.

4

Hi infdude666;

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

-Scott