Shulou(Shulou.com)05/31 Report--

This article is about how to use EXPLAIN syntax in SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The possible_keys:possible_keys column indicates which index MySQL can use to find rows in the table. Note that this column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot actually be used in the order of the generated table.

The key:key column shows the key (index) that MySQL actually decides to use. If no index is selected, the key is NULL. To force MySQL to use or ignore indexes in possible_keys columns, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

The key_len:key_len column shows the key length that MySQL decides to use. If the key is NULL, the length is NULL. Note that from the key_len value we can determine how many parts of a multiple keyword MySQL will actually use.

The ref:ref column shows which column or constant is used to select rows from the table with key.

The rows:rows column shows the number of rows that MySQL thinks it must check when executing the query.

Extra: this column contains the details of the query resolved by MySQL.

When Distinct:MySQL finds the first matching row, it stops searching for more rows for the current row combination.

Not exists:MySQL can LEFT JOIN optimize the query, and after finding a row that matches the LEFT JOIN standard, no more rows are checked in the table for the previous combination of rows.

Range checked for each record (index map: #): MySQL did not find a good index to use, but found that some indexes might be available if the column values from the previous table were known. For each row combination of the previous table, MySQL checks to see if it is possible to use the range or index_merge access method to request the row.

Using filesort:MySQL needs an extra pass to figure out how to retrieve rows in sort order. Sorting is done by browsing all rows according to the join type and saving the sort keyword and row pointers for all rows that match the WHERE clause. The keywords are then sorted and the rows are retrieved in sort order.

Using index: retrieve the column information in the table by reading the actual rows from using only the information in the index tree without further search. This strategy can be used when a query uses only columns that are part of a single index.

Using temporary: to solve the query, MySQL needs to create a temporary table to hold the results. Typically, such as when a query contains GROUP BY and ORDER BY clauses that can list columns according to different situations.

The Using where:WHERE clause is used to restrict which row matches the next table or is sent to the customer. Unless you specifically retrieve or check all rows from the table, if the Extra value is not Using where and the table join type is ALL or index, the query may have some errors.

Using sort_union (...), Using union (...), Using intersect (...): these functions show how to merge index scans for index_merge join types.

Using index for group-by: similar to the Using index way of accessing tables, Using index for group-by indicates that MySQL has found an index that can be used to query all columns of a GROUP BY or DISTINCT query without additional searching the hard disk to access the actual table. Also, use the index in the most efficient way so that only a small number of index entries are read for each group.

By multiplying all the values of the rows column output by EXPLAIN, you can get a hint about how to join. This should give you a rough idea of how many rows MySQL must check to execute the query. When you use the max_join_size variable to restrict the query, you also use this product to determine which multi-table SELECT statement to execute.

Thank you for reading! This is the end of the article on "how to use EXPLAIN Grammar in SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.