mysql - Using filesort in simple query -

i have simple table:

create table `navigation` (   `id` int(11) unsigned not null auto_increment,   `parent_id` int(11) unsigned default null,   `title` varchar(255) not null comment 'Название ссылки',   `priority` tinyint(3) not null comment 'Параметр сортировки'   primary key (`id`) ) engine=innodb; 

with 41 rows. have simple query:

mysql> explain select t.id, t.parent_id, t.title, t.priority navigation t order t.priority asc; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows |          | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ |  1 | simple      | t     |  | null          | null | null    | null |   41 | using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) 

how can avoid using filesort? or impossible? have read lot of topics on so, couldn't understand right answer. thank you.

how can avoid using filesort? or impossible? have read lot of topics on so, couldn't understand right answer.

you need index on priority column:

alter table navigation add index (priority); 

however, chances mysql calculate using such index sort results more expensive filesort (since former involve sequentially reading index file in order perform random i/o table, whereas latter involve sequentially reading table , performing in memory sort on results). can override assessment index hint:

select   t.id, t.parent_id, t.title, t.priority     navigation t force index order (priority) order t.priority asc; 

a covering index altogether avoid need read table , return results merely walking sequentially through index file; therefore selected query optimiser without further hinting:

alter table navigation add index(priority, id, parent_id, title); 

which approach right depend on application's requirements, remember knuth's maxim: "premature optimisation root of evil".