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