Explain key parameters for MySQL

Source: Internet
Author: User

First, the key parameter type

System best, to ref, generally considered to be the standard.
All and index are the worst and must be optimized.

The join type. Here are the various join types, sorted by best type to worst type:

System: Table has only one row (= system table). This is a special case of the const join type.

Const: The table has a maximum of one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constants by the remainder of the optimizer. The const table is fast because they are read only once!

eq_ref: Reads a row from the table for each row combination from the preceding table. This may be the best type of join, except for the const type.

ref: For each row combination from the preceding table, all rows with matching index values are read from this table.

ref_or_null: The join type is like ref, but adding MySQL can specifically search for rows that contain null values.

index_merge: The join type represents the use of the index merge optimization method.

unique_subquery: This type replaces the Ref:value in subquery of the following form (SELECT Primary_key from single_table WHERE some_expr) Unique_subquery: is an index lookup function that can completely replace a subquery and be more efficient.

index_subquery: The join type is similar to Unique_subquery. You can replace in subqueries, but only for non-unique indexes in subqueries of the following form: value in (SELECT key_column from single_table WHERE some_expr)

Range: Retrieves only the rows for a given range, using an index to select rows.

index: The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.

All: A complete table scan for each row combination from the previous table.

Second, key parameters Possible_keys, key

Possible_keys: Indicates the index that may be used
Key: Indicates the actual index used

Extra of key parameters

In general, the use of the using Filesort and the using temporary need to be optimized.

This column contains the details of the MySQL resolution query

Distinct:mysql finds a 1th matching row, stops searching for more rows for the current row combination.

Not Exists:mysql can perform a left join optimization on the query, and after discovering 1 rows that match the left join standard, no more rows are checked in the table for the preceding row.

Range checked for each record (Index map: #): MySQL did not find a good index to use, but found that if the column values from the previous table were known, some indexes might be available.

The Using Filesort:mysql requires an additional pass to find out how rows are retrieved in sorted order.

Using index: Retrieves column information from a table by using only the information in the index tree without requiring further search to read the actual rows.

Using Temporary: In order to resolve the query, MySQL needs to create a temporary table to accommodate the results.

The Using where:where clause is used to restrict which row matches the next table or send to the customer.

Using Sort_union (...), using Union (...), using intersect (...): These functions describe how to merge index scans for Index_merge join types.

Using index for group-by: similar to using index for accessing tables, using index for group-by means that MySQL has found an index that can be used to query all the columns of a group by or distinct query. Instead of additional search hard disk access the actual table.

Explain key parameters for MySQL

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.