Summary of slow query analysis caused by MySQL prefix index _ MySQL

Summary of slow query analysis caused by MySQL prefix indexes: the frontend time of bitsCN.com is a DB-related project. alanc reports that using indexes is much slower than not using indexes. 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 | 2010066 |
+ ---- + ------------- + ------ + --------------- + ------ + --------- + ------- +

However, the query results using the index order by are as follows, which results in an astonishing slow speed.
Mysql> explain select * from rosterusers order by username limit, 3;
+ ---- + ------------- + ------ + --------------- + ------ + --------- + ------------------ +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------ + --------------- + ------ + --------- + ------------------ +
| 1 | SIMPLE | rosterusers | ALL | NULL | 2010087 | Using filesort |
+ ---- + ------------- + ------ + --------------- + ------ + --------- + ------------------ +

The difference is that the Extra used for index query 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 the primary key definition is not the complete primary key name defined, but with a 75 length description, a slight glimpse, the original uses a prefix index, instead of indexing 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 index is a method that uses the first N bytes in the data field as the index.

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.

Summary:
• 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, it is important to properly plan the length and type of fields for DB Schema definitions. BitsCN.com