Mysql optimized index -- Using filesort

Mysql optimized index -- when Using filesort uses Explain to analyze SQL statements, it is often found that Using filesort appears in some statements in the Extra column. According to the description in the mysql official documentation: www.2cto.com references MySQL must do an extra pass to find out how to retrieve the rows in sorted order. the sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. the translation in the Chinese manual is awkward: reference "Mysql requires an additional transfer to find out how to retrieve rows in order by the root Browse all rows based on the join type and save the sorting keywords and row pointers for all rows matching the where clause to complete the sorting. Then, the keywords are sorted and the rows are retrieved in the sorting order ." In general, Using filesort is a slow external sorting in Mysql. If it can be avoided, we can optimize indexes to avoid Using filesort, this increases the speed. Here is a simple example: create table 'testing' ('id' int (10) unsigned not null auto_increment, 'room _ number' int (10) unsigned not null default '0', primary key ('id'), KEY 'room _ number' ('room _ number ')) ENGINE = MyISAM default charset = latin1 www.2cto.com write a stored PROCEDURE askwan, insert 0.1 million pieces of test data mysql> DELIMITER $ drop procedure if exists 'askwan '. 'askwan '$ create procedure 'askwan '. 'askwan '() begin declare v int default 1; WH ILE v <100000; do insert into testing VALUES (v, v); SET v = v + 1; end while; END $ mysql> DELIMITER; mysql> CALL askwan (); query OK, 1 row affected (13.21 sec) OK, data is ready, start the test. Based on the table information created in the preceding example, I have created two indexes, one primary key id and one room_number column index. Now let's look at an SQL statement, SELECT id FROM testing WHERE room_number = 1000 order by id; analyze mysql> explain select id FROM testing WHERE room_number = 1000 order by id; + ---- + ------------- + --------- + ------ + --------------- + ------------- + --------- + ------- + ------ + keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + --------- + ------ + --------------- + ----------- + --------- + ------- + ------ + accept + | 1 | SIMPLE | testing | ref | room_number | 4 | const | 1 | Using where; using filesort | + ---- + ------------- + --------- + ------ + --------------- + ----------- + --------- + ------- + ------ + rows + 1 row in set (0.00 sec) www.2cto.com Ng filesort, And the room_number column index is used. However, the index used here is for the room_number condition after the WHERE clause, and the last sorting is based on the id, this is what the manual says, "An additional sort "!, Then there will be Using filesort. Based on an article I have previously written, I will create a joint index room_number_id alter table testing add index room_number_id (room_number, id ); next, let's analyze mysql> explain select id FROM testing WHERE room_number = 1000 order by id; + ---- + ------------- + --------- + ------ + upper + ---------------- + --------- + ------- + ------ + ---------------------------- + | id | select_type | table | type | possible_keys | key | key_len | Ref | rows | Extra | + ---- + ------------- + --------- + ------ + upper + ---------------- + --------- + ------- + ------ + ------------------------ + | 1 | SIMPLE | testing | ref | room_number, room_number_id | 4 | const | 1 | Using where; | + ---- + ------------- + --------- + ------ + accept + ---------------- + --------- + ------- + ------ + -------------------------- + 1 row in set (0.00 sec) now Using filesort is gone. To sum up www.2cto.com: 1. generally, there are order by statements. When the index is improperly added, Using filesort may occur. At this time, we need to optimize the SQL statements and indexes. However, this is not to say that the emergence of Using filesort is a serious problem. This is not the case. The example here is extreme, and it is almost impossible to have such a foolish query, optimization, or non-optimization, it depends on whether it affects business performance. 2. from the above we can see the Union Index, which can also be called a multi-column index, for example, key ('a1', 'a2 ', 'a3', 'a4, the general idea of sorting is to first sort by A1, A1 is the same, and then A2, and so on. In this way, for (A1), (A1, A2), (A1, A2, a3) indexes are valid, but the indexes such as (A2, A3) are invalid.