Slow query caused by incorrect MySQL prefix Index

Slow query caused by incorrect use of MySQL prefix index the front-end time is related to a DB project. alanc reports that there is a query. Using indexes is much slower than not using indexes, which is a little ruined. So I followed up and used explain to look at two different query results. The results of queries without indexes are as follows. The actual query speed is relatively small. Mysql> explain select * from rosterusers limit, 3; + ---- + ------------- + ------ + --------- + ------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------ + --------------- + ------ + --------- + ------- + | 1 | SIMPLE | rosterusers | ALL | NULL | NULL | 2010066 | + ---- + ------------- + ----------- + ------ + --------------- + ------ + --------- + ------- + the query result using the index order by is as follows, the speed is rather slow. Mysql> explain select * from rosterusers order by username limit, 3; + ---- + ------------- + ------ + --------- + ------------------ + | id | select_type | table | type | keys | key_len | ref | rows | extra | + ---- + ------------- + ------ + --------------- + ------ + --------- + ------------------ + | 1 | SIMPLE | ros Terusers | ALL | NULL | 2010087 | Using filesort | + ---- + ------------- + ------ + --------- + ---------------- +, the index query Extra is changed to Using filesort. External files are used for sorting. Of course this is slow. However, the username in the data table is indeed indexed. How can we use Using filesort instead? Let's take a look at the data table definition. Is a table of ejabberd, an open-source chat server. Initially, I thought that the primary key I _rosteru_user_jid is username, and the joint index with jid should be available when order by username is used? Create table 'rosterusers' ('username' varchar (250) not null, 'jid' varchar (250) not null, unique key 'I _ rosteru_user_jid' ('username' (75), 'jid' (75), KEY 'I _ rosteru_jid' ('jid ')) ENGINE = InnoDB default charset = utf8; check carefully and suddenly find that its primary key definition is not the complete primary key name defined, but with a 75-length description, just a moment, the original index is a prefix instead of an index for the entire field. (InnoDB does not support this feature in my memory. It is estimated that InnoDB is added to any version later than 4.0 ), prefix indexes use the first N bytes of data fields as indexes .. After finding this problem, we began to suspect that slow queries are related to this index. The primary purpose of prefix indexes is sometimes the field process, and many of the index lengths supported by MySQL are limited. First, the limit query without order by may be related to the primary key in nature, because the INNODB operations of MySQL depend on the primary key (even if you have not created, the system will also have a default value), while the use of the primary key for limit queries can speed up (the rows returned by the explain statement should be a reference value ), although I have not seen any documentation clearly explaining this problem, the return results of limit queries without order by can basically prove this. However, when we use order by username, we want to use the username sorting instead of the username (75) sorting, but the actual index is the prefix index, not the index of the complete field. Therefore, the index cannot be used when order by is created. (I add a mandatory index I _rosteru_user_jid in the SQL statement ). In fact, in use, the username field in the table cannot be used for 75 connections, not to mention the defined length of 250. It is all the trouble caused by self-tossing. Because this is a table of other products, we cannot change it. For the time being, we can only pay attention to the query without sorting. Conclusion: prefix indexing is not a panacea. It can indeed help us create an index on a field that is too long to write. However, the prefix index cannot be used for sorting (order by, group by) queries. At any time, for DB Schema definition, reasonable planning of their own field length, field type is the first thing.