Front-End time with a DB-related project, Alanc feedback has a query that uses indexes that are many times slower than not using indexes, a bit of a ruin. So follow up, with explain, look at 2 different results of the query.
Without indexing the results of the query are as follows, the actual query speed comparison block.
Copy Code code as follows:
Mysql> Explain select * from Rosterusers limit 10000,3;
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| 1 | Simple | Rosterusers | All | NULL | NULL | NULL | NULL | 2010066 | |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
The results of the query using the index order by are as follows, but the speed is surprisingly slow.
Mysql> Explain select * from Rosterusers ORDER by username limit 10000,3;
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| 1 | Simple | Rosterusers | All | NULL | NULL | NULL | NULL | 2010087 | Using Filesort |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
The difference is that the extra that uses the index query becomes the using Filesort. Actually use the external file to sort. Of course it's slow.
But on the data table in the username, it does have an index. How would you rather have a using filesort?
Look at the data table definition. is an open source chat server ejabberd a table. First look at the primary key I_rosteru_user_jid is username, and Jid of the joint index, then use the order by username should be used to the index?
Copy Code code as follows:
CREATE TABLE ' Rosterusers ' (
' username ' varchar not NULL,
' Jid ' varchar not NULL,
UNIQUE KEY ' I_rosteru_user_jid ' (' username ' (), ' Jid ' (75)),
KEY ' I_rosteru_jid ' (' Jid ')
) Engine=innodb DEFAULT Charset=utf8;
A careful examination of the primary key definition, which is not defined as the complete primary key name, is followed by a 75 length description, slightly stunned, with the prefix index, rather than the entire field being indexed. (In my memory InnoDB does not support this thing, estimate is 4.0 after what version added), the prefix index is a data field in the front n bytes as an index of the way.
After discovering this problem, we began to suspect that the slow query is related to this index, and that the main purpose of the prefix index is sometimes the field process, and many of the index lengths that MySQL supports are limited.
This query is first limit without an order by the essence may still be related to the primary key, because MySQL's innodb operations are actually dependent on the primary key (even if you are not established, the system will have a default), and limit this query, using the primary key can be faster, (The rows returned by explain should be a reference value), although I don't see a document that explicitly describes the problem, the return result of a limit query that never takes an order by is basically proof of this.
But when we use ORDER by username, we want to use the sort of username instead of username (75), but the actual index is the prefix index, not the full field index. Therefore, the index is completely unusable by the time it is created. (I added mandatory use index in SQL statement I_rosteru_user_jid also does not work). In fact, in use, the table in the field username even 75 are not used, not to mention the definition of the length of 250. It's all a hassle caused by your own toss. As this is the other products of the table, we can not change, for the time being can only be used without sorting the query pay attention.
Summary：
• Prefix index, not a panacea, he does help us index a field that is long written. It can also cause a sort (order by, group by) query to not use the prefix index.
• At any time, for the DB schema definition, it is important to plan your own field length reasonably.
