About how to automatically add a primary key in the index of MySQL InnoDB

Source: Internet
Author: User
(I) principle:

As long as the index field defined by the user contains the field in the primary key, this field will not be automatically added to the index by InnoDB.
However, if the index field does not fully contain the primary key field, InnoDB adds the remaining primary key fields to the end of the index.


(Ii) Example

Example 1:

CREATE TABLE t (  a char(32) not null primary key,  b char(32) not null,  KEY idx1 (a,b),  KEY idx2 (b,a)) Engine=InnoDB;

Idx1 and idx2 indexes have the same internal size and no difference.


Example 2:

CREATE TABLE t (  a char(32) not null,  b char(32) not null,  c char(32) not null,  d char(32) not null,  PRIMARY KEY (a,b)  KEY idx1 (c,a),  KEY idx2 (d,b)) Engine=InnoDB;

In this table, InnoDB will automatically complete the primary key dictionary. idx1 actually stores (C, A, B) internally, and idx2 actually stores (D, B, A) internally)
However, the automatically added field and server layer are unknown, so the MySQL optimizer does not know the existence of this field. If you have a query:

   SELECT * FROM t WHERE d=x1 AND b=x2 ORDER BY a;


In fact, the idx2 (D, B, A) in the internal storage can make this query completely indexed, but the server layer does not know,
Therefore, the MySQL Optimizer may select idx2 (D, B) to filter and sort field a, or directly use PK scan to avoid sorting.

If we define the table structure as key idx2 (D, B, A), MySQL will know that all the three fields (D, B, A) are indexed,
In addition, InnoDB found that the user-defined index contains all primary key fields, and no more storage space is added.


(Iii) Suggestions

Therefore, we sincerely recommend that you add the primary key field and,
There is no loss, but it may surprise you.

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.