table
Table access is via an access_type which describes how the table is accessed or joined to other tables in the query.
Detailed Description
Table access is via an access_type which describes how the table is accessed or joined to other tables in the query.
There are other properties such as the table_name, query_cost, rows_examined_per_scan and several others depending on the access_type which then give further details about the efficiency of the access type.
Table access types (access_type)
The following list describes the access types, ordered from the best type to the worst:
system
The system access type is a special type of const join type on a system table with only one row. This is a very fast access.
MySQL Documentation - access type system
const
The const access type is accessing a table with at most one row in. This table is ready at the start of the query and is very fast due to only being read once. const accesses can be seen reading a single value from a primary key or a unique index.
MySQL Documentation - access type const
eq_ref
The eq_ref access method joins a single row in the first data source with a single row in the second data source. This is a very fast access method as it requires primary keys or unique not null indexes as sources of data.
MySQL Documentation - access type eq_ref
ref
The ref access method is used to join data sources where uniqueness cannot be guaranteed. Where the access key returns a low number of records, this access method performs well.
MySQL Documentation - access type ref
fulltext
The fulltext join type uses a FULLTEXT index to join.
MySQL Documentation - access type fulltext
ref_or_null
Similar to ref join access, but the ref_or_null has the ability to do an extra search for NULL values. This join method is often used with subqueries and to search for NULL values.
MySQL Documentation - access type ref_or_null
index_merge
An index_merge join method is in use. The returned key column contains a list of indexes to be used.
MySQL Documentation - access type index_merge
unique_subquery
The unique_subquery join type is an index lookup and replaces the eq_ref method for some IN subqueries. Like eq_ref, unique_subquery requires primary keys or unique not null indexes.
MySQL Documentation - access type unique_subquery
index_subquery
Similar to unique_subquery, index_subquery replaces certain IN subqueries, but it can be used for non-unique indexes.
MySQL Documentation - access type index_subquery
range
The range join type retrieves a given range of rows using an index to select the rows. range can be used where the key column is compared to a constant.
MySQL Documentation - access type range
index
The index join type can be used if all the required columns are covered by indexes. index can be used where all the queried columns are in a single index.
MySQL Documentation - access type index
ALL
The ALL join type carries out a full table scan for each combination of rows from the previous table. Performance of ALL queries can be very bad and are best avoided. This can be done by adding indexes that support the query.
MySQL Documentation - access type ALL
Other useful properties
table_name
The table_name is the row source for the query block. It can be:
- an actual table name
- a union shown as
<union*M, N*> - a derived row source shown as
<derived*N*> - a subquery shown as
<subquery*N*>
partitions
Shows the partitions from which rows match the query. For non-partitioned tables, the value is NULL.
MySQL Documentation - Explain partitions
key
The key column shows the actual index selected by the planner to satisfy this part of the query. This is usually from the values show in the possible_keys column but it is possible that a scan of an index covering all required columns that is not in the possible list will be used.
You can use FORCE INDEX, USE INDEX or IGNORE INDEX query hints to use or ignore specific indexes.
If key is NULL then no suitable index was found.
MySQL Documentation - Explain key
rows_examined_per_scan
The rows_examined_per_scan column indicates the number rows scanned in this section of the query. When used with the filtered column and the rows_prodcued_per_join column, you can get an understanding of how much data your query will read and how selective your query is.
If filtered is a low percentage and rows_examined_per_scan is much higher than rows_produced_per_join then it is possible your query efficiency could be improved by indexing.
rows_produced_per_join
The rows_prodcued_per_join column indicates the number rows returned in this section of the query. When used with the filtered column and the rows_examined_per_scan column, you can get an understanding of how much data your query will read and how selective your query is.
If filtered is a low percentage and rows_examined_per_scan is much higher than rows_produced_per_join then it is possible your query efficiency could be improved by indexing.
rows
The rows column indicates the estimated number of the the planner believes will be returned by the query block. With InnoDB tables this is an estimate only.
filtered
The filtered column indicates an estimated percentage of table rows that are filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred.
MySQL Documentation - Explain filtered
read_cost
Cost of reading for each table used in the query block.
eval_cost
Cost of condition evaluation for each table in the query block.
prefix_cost
Cost of executing prefix join in the query block.
query_cost
TODO
data_read_per_join
Estimated amount of data from the table processed during single query block execution.
using_index
This will be true or false to indicate whether an index is being used.
possible_keys
The possible_keys column shows the indexes which may be used to satisfy the query. If this column is NULL, there are no usable indexes and your query may benefit from adding an index to the queried columns. Beware of adding too many indexes as they will add an overhead when to future INSERT/UPDATE/DELETE operations.
MySQL Documentation - Explain possible keys
used_key_parts
Composite indexes are just indexes on multiple columns. used_key_parts shows which part of a multiple column key is being used.
key_length
The key_length column shows the length of the key the planner picked for the query. This shows if only part of a multiple part key was used.
If key is NULL then key_length will be NULL and no suitable index was found.
MySQL Documentation - Explain key_len
used_columns
List of columns of the table (per each table in the query block) used for either read or write in the query. If the number of columns in used_columns is reasonably small, you can use it as a guide for creating a covered index.
ref
The ref column in the query block shows which columns or constants are compared with the key column to select rows. If func is returned, then a function or arithmetic operator is being used.
MySQL Documentation - Explain ref
Additional Links
Search online
If this article doesn't have the information you need you can try searching online. Remember, you can contribute suggestions to this page.