Tuesday, August 23, 2011

The MySQL range access method explained

The range access method uses an index to read a subset of rows that form one or multiple continuous index value intervals. The intervals are defined by the query's range predicates, which are comparisons using any of =, <=>, IN(), IS NULL, IS NOT NULL, >, <, >=, <=, BETWEEN, !=, <> or LIKE.

Some examples:
SELECT * FROM blog WHERE author_id IN (1, 7, 8, 10)
SELECT * FROM orders WHERE value > 1000

You know that the range access method is used when EXPLAIN shows type=range.

Naturally, there has to be an index on the column used by the range predicate. Since indexes are ordered, MySQL will, for each interval, dive down the index using the interval start value and read it's way through the index leaves until it reaches the interval end value:



Wednesday, August 17, 2011

The meaning of ref=func in MySQL EXPLAIN

When EXPLAIN shows that a table is accessed using the [eq_]ref access type, we're used to look at the ref column to see where MySQL gets the value to look up from. Usually, we see either "const" if the value is provided as a constant in the query or a column name if the value is read from a column in an already read table:

EXPLAIN 
SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber
FROM accounts_receivable as acc_rec
WHERE acc_rec.cust_id=1;
+----+-------------+---------+------+---------+-------+
| id | select_type | table   | type | key     | ref   |
+----+-------------+---------+------+---------+-------+
| 1  | SIMPLE      | acc_rec | ref  | cust_id | const |
+----+-------------+---------+------+---------+-------+

EXPLAIN 
SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber,
       customers.firstname, customers.lastname, customers.phone
FROM accounts_receivable AS acc_rec JOIN customers AS cust
ON acc_rec.cust_id = customers.cust_id;
+----+-------------+---------+--------+---------+-----------------+
| id | select_type | table   | type   | key     | ref             |
+----+-------------+---------+--------+---------+-----------------+
| 1  | SIMPLE      | acc_rec | ALL    | NULL    | NULL            |
| 1  | SIMPLE      | cust    | eq_ref | PRIMARY | acc_rec.cust_id |
+----+-------------+---------+--------+---------+-----------------+

But what if ref shows the value "func"? In this case, the value used as input to [eq_]ref is the output of some function. A few examples: