Samsung evaluation of MySQL index (Three-star system)

Source: Internet
Author: User
Tags mysql index

In the third edition of High-performance MySQL, the evaluation of indexes is mentioned: Samsung Evaluation (Three-star system).

In the original English version of "High Performance MySQL (3rd Edition)" The third edition is:

The index earns one star if it places relevant rows adjacent to each other, 
A second star if its rows is sor Ted in the order of the query needs,
and a final star if it contains all the columns needed for the query.

This paragraph is very confusing to understand, the third edition of Chinese translation is not good understanding:

The index places the related records together to obtain a single star;

If the order of the data in the index and the order in the lookup are consistent, two stars are obtained;

If the column in the index contains all the columns needed in the query, get Samsung;

The individual expresses both Chinese and English are very understanding (whether Chinese and English are taught by the maths teacher, ^...^).

I have a deep question about this:

One star: What does the relevant record mean?? (The word is very esoteric, what is the department of "related department")

Two stars: If the B-tree (B+tree) index is established, the data is ordered.
Samsung: The indexed column contains all the columns that the query requires? There is no need to index all the columns on the WHERE query condition!

I think that the one-and two-star rows should be columns, and the index is not related to the specific data rows, but only to the columns of the query. This is also in line with the view of the multi-column index mentioned later in the high performance MySQL, in particular the two-star evaluation.

Personal point of view:

Evaluate whether an index is suitable for a "Samsung system" (Three-start systems) for a query:
One-star: The index puts the related columns together, that is, to index on a column of necessary columns, without having to index the columns in the Where condition.
Two-star: the Order of data columns in the index is the same as the order of the rows in the lookup. Typically, the highest-selectivity columns are placed at the forefront of the index.
Samsung: The columns in the index contain all the columns that are required in the query. The index contains the data columns required by the query and no longer a full table lookup (clustered index, overwrite index).

Resources:

"High performance MySQL" Chinese third edition

"High Performance MySQL (3rd Edition)" in English third edition


Samsung evaluation of MySQL index (Three-star system)

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.