MySQL Forums
Forum List  »  Optimizer & Parser

Re: Explain plan--join type
Posted by: Jørgen Løland
Date: October 07, 2011 12:41AM

Hi Amy,

Welcome to the forum. First, let me suggest you move to a newer version of the manual (unless you're actually using MySQL 5.0).

> 1. const:If the table has at most one matching row, then why the article stated
> "const is used when you compare ALL parts of a PRIMARY KEY or UNIQUE index to
> constant values". What does it mean "ALL parts of the PK"?

If a table has exactly one row, the table type is "system". If the table has more than one row but the query contains conditions so that at most one of those rows will match the conditions, the table type is "const".

Consider this simple table:

CREATE TABLE person (
personnbr int,
name varchar (128),
PRIMARY KEY (personnbr)
)

Then in the following query, "person" will be of type const because it is known that only one person can match any personnumber (since it is primary key):

SELECT * FROM person WHERE personnbr=123456789;

However, it is possible to create multi-column indexes in MySQL [1]:

CREATE TABLE car_rental (
car_id int,
rental_date date,
...,
PRIMARY KEY (car_id, rental_date)
)

Assuming car_rental contains more than one row, the query

SELECT * FROM car_rental WHERE car_id=5;

may return more than one row since each car can be rented out many time (on different dates) while

SELECT * FROM car_rental WHERE car_id=5 AND rental_date='2011-09-16';

may only return one row since each car can only be rented out once per day. Hence, in this query the "car_rental" table will have type const.

Notice that the primary key consists of two columns, and both are specified in the query. That's what "all parts of the index" means.

> 2. eq_ref: what does it really mean "One row is read from the table for each
> combination of rows from the previous tables"? Can someone elaborate a bit?

Consider this query, using the table "person" as sketched above:

SELECT * FROM offices JOIN person ON offices.personid=person.personnbr;

In this query, each office houses exactly one person. So, assuming offices is shown first in EXPLAIN, the following happens inside MySQL:

A row is read from offices. The personid of that row is used to do an index lookup in person on the personnbr column. Since personnbr is known to be unique (primary key), the type is eq_ref.

> 3. ref:

Consider this query, using the table "car_rental" as sketched above:

SELECT * FROM car JOIN car_rental on car.car_id=car_rental.car_id;

Assuming car is shown first in EXPLAIN, the following happens:

A row is read from car. The car_id is used to do index lookup in car_rental but since there can be many car_rental rows with the same car_id the type is "ref".

So...
* eq_ref is for joins where a lookup is done in the index and at most one row will match.
* ref is for joins where a lookup is done in the index and multiple rows may match.

[1] http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
2285
October 05, 2011 11:10PM
Re: Explain plan--join type
994
October 07, 2011 12:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.