Learning Mysql

Well now give the
EXPLAIN
statement some work to do. Let’s ask it to explain an
INNER
JOIN
between artist and album
:
mysql> EXPLAIN SELECT * FROM artist INNER JOIN album USING (artist_id);
+----+-------------+--------+------+---------------+...
| id | select_type | table | type | possible_keys |...
+----+-------------+--------+------+---------------+...
| 1 | SIMPLE | artist | ALL | PRIMARY |...
| 1 | SIMPLE | album | ref | PRIMARY |...
+----+-------------+--------+------+---------------+...
...+---------+---------+------------------------+------+-------+
...| key | key_len | ref | rows | Extra |
...+---------+---------+------------------------+------+-------+
...| | | | 6 | |
...| PRIMARY | 2 | music.artist.artist_id | 1 | |
...+---------+---------+------------------------+------+-------+
2 rows inset sec)
Before we discuss the output, think about how the query could be evaluated. MySQL
could go through each row in the artist table and lookup the album table to see what rows match. Or it could go through each row in the album table and lookup the artist table to see what rows match. Let’s see what MySQL has decided to do. This time, there are two rows because there are two tables in the join. Let’s run through this,
focusing on those things that are different from the previous example The first row is basically identical to the previous example. All rows in the artist table are processed, so MySQL has decided that the same method of solving the query is its preferred way here, too The join type for the album table is ref, meaning that all rows in the album table that match rows in the artist table will be read. In practice, this means one or more rows from the album table will be read for each artist_id
• The possible_keys for artist and album are both only the
PRIMARY
key. A key isn’t used in artist
(because we’re scanning the whole table, but the key used for album is that table’s
PRIMARY
key
• The primary key used to search album has a key_len of 2 and is searched using the music.artist.artist_id value from the artist table
Again, this seems like a sensible strategy, and it fits with what we thought about in our design of the database.

Download 4.24 Mb.

Share with your friends: