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:



A string type with a different character set

CREATE TABLE accounts_receivable (..., cust_id VARCHAR, ...) DEFAULT CHARSET=utf8;
CREATE TABLE customers (..., cust_id VARCHAR, ...) DEFAULT CHARSET=latin1;

EXPLAIN EXTENDED
SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber,
       cust.firstname, cust.lastname, cust.phone
FROM customers AS cust LEFT JOIN accounts_receivable AS acc_rec 
ON acc_rec.cust_id = cust.cust_id;
+----+-------------+---------+------+---------+------+
| id | select_type | table   | type | key     | ref  |
+----+-------------+---------+------+---------+------+
| 1  | SIMPLE      | cust    | ALL  | NULL    | NULL |
| 1  | SIMPLE      | acc_rec | ref  | cust_id | func |
+----+-------------+---------+------+---------+------+
Warnings:
Note 1003  /* select#1 */ select `mydb`.`acc_rec`.`cust_id` AS `cust_id`, 
`mydb`.`acc_rec`.`amount` AS `amount`, `mydb`.`acc_rec`.`invoiceNumber` AS 
`invoicenumber`,`mydb`.`cust`.`firstname` AS `firstname`,`mydb`.`cust`.
`lastname` AS `lastname`,`mydb`.`cust`.`phone` AS `phone` from `mydb`.
`customers` `cust` left join `mydb`.`accounts_receivable` `acc_rec` 
on((`mydb`.`acc_rec`.`cust_id` = convert(`mydb`.`cust`.`cust_id` using utf8))) 
where 1

An arithmetic function

CREATE TABLE accounts_receivable (..., cust_id INT, ...) ;
CREATE TABLE customers (..., cust_id INT, ...) ;

EXPLAIN EXTENDED
SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber, 
       cust.firstname, cust.lastname, cust.phone
FROM accounts_receivable AS acc_rec JOIN customers AS cust
ON (acc_rec.cust_id-1) = cust.cust_id;
+----+-------------+---------+--------+---------+------+
| id | select_type | table   | type   | key     | ref  |
+----+-------------+---------+--------+---------+------+
| 1  | SIMPLE      | acc_rec | ALL    | NULL    | NULL |
| 1  | SIMPLE      | cust    | eq_ref | PRIMARY | func |
+----+-------------+---------+--------+---------+------+
Note 1003  /* select#1 */ select `test`.`acc_rec`.`cust_id` AS `cust_id`,
`test`.`acc_rec`.`amount` AS `amount`,`test`.`acc_rec`.`invoiceNumber` AS 
`invoicenumber`,`test`.`cust`.`firstname` AS `firstname`,`test`.`cust`.
`lastname` AS `lastname`,`test`.`cust`.`phone` AS `phone` from `test`.
`accounts_receivable` `acc_rec` join `test`.`customers` `cust` where 
((`test`.`acc_rec`.`cust_id` - 1) = `test`.`cust`.`cust_id`)

In both cases, EXPLAIN EXTENDED tells you 1) which column is used as input to the function and 2) what the function does.

No comments:

Post a Comment