sql - MySQL: Avoid filesort when using ORDER BY -
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 clause; when explain on below query doing filesort , runtime limit take 10seconds high. mysql> explain select * db1.table1 force index(index1) kid=187 , table_group in ('not_present', 'nothing', 'perror') order 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 avoid filesort; please help