Skip to main content

table

Short Description

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 <unionM, N>
  • a derived row source shown as <derivedN>
  • a subquery shown as <subqueryN>

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

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.

DBmarlin Trial