Access Methods

Access Methods

The type Column and Access Methods

type, how MySQL 524 finds a table's rows, is the first column to check. This function prints five columns of the traditional plan (table, type, key, rows, Extra):

A plan() helper, and one query per access methodSQL

plan() { sudo mysql shop -N -e "EXPLAIN FORMAT=TRADITIONAL $1" | cut -f3,5,7,10,12; }
{
  plan "SELECT * FROM orders WHERE id = 42"
  plan "SELECT * FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id
        WHERE oi.product_id = 7"
  plan "SELECT * FROM orders WHERE customer_id BETWEEN 10 AND 20"
  plan "SELECT * FROM orders WHERE customer_id = 5 OR id = 9"
  plan "SELECT COUNT(*) FROM orders"
  plan "SELECT * FROM orders WHERE status = 'pending'"
} | column -t -s $'\t'

Output

orders  const        PRIMARY              1       NULL
oi      ref          product_id           52      NULL
o       eq_ref       PRIMARY              1       NULL
orders  range        customer_id          62      Using index condition
orders  index_merge  customer_id,PRIMARY  7       Using union(customer_id,PRIMARY); Using where
orders  index        customer_id          499223  Using index
orders  ALL          NULL                 499223  Using where

From best to worst: const reads one row by a unique key, once, at the start; eq_ref one row per outer row by a unique key, the ideal inner side of a join; ref the rows matching a non-unique index; range a bounded slice (<, BETWEEN, IN, LIKE 'x%'); index_merge a union of lookups on two indexes; index a whole index (COUNT(*) picks the smallest); ALL the whole table. Down to range, cost follows the rows you ask for. index and ALL follow table size, harmless on a lookup table and the first thing to fix on a large driving table.

MENU