Databases Reference
In-Depth Information
We'll 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 in set (0.01 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 look up the
album
table to see what
rows match. Or it could go through each row in the
album
table and look up 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.
Exercises
1. Write the monitor command to import the file
academics.tsv
, which has its values
separated by tabs, into the
details
table. Hint: the tab character is shown with the
\t
escape sequence.
2. When would you need to insert data using a query?
3. What's the difference between
REPLACE
and
INSERT
IGNORE
?
