Not the answer you need?
Register and ask your own question!

MySQL: Avoid filesort when using ORDER BY LIMIT

infdude666infdude666 ContributorCurrent User Role Beginner
     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


  • wagnerbianchiwagnerbianchi Remote DBA Current User Role Patron
    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 <table> 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?

  • infdude666infdude666 Contributor Current User Role Beginner
    Hi 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;
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi infdude666;

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

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.