Slow query Analysis summary caused by MYSQL prefix index _mysql

Source: Internet
Author: User
Tags table definition
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.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.