Specifications

There are several ways you can fix problems you spot in the output from EXPLAIN.
First, check column types and make sure they are the same. This applies particularly to column
width. Indexes can’t be used to match columns if they have different widths. You can fix this
by changing the types of columns to match, or building this in to your design to begin with.
Second, you can tell the join optimizer to examine key distributions and therefore optimize
joins more efficiently using the myisamchk utility. You can invoke this by typing
>myisamchk --analyze pathtomysqldatabase/table
You can check multiple tables by listing them all on the command line, or by using
>myisamchk --analyze pathtomysqldatabase/*.MYI
You can check all tables in all databases by running the following, which will produce the out-
put shown in Figure 11.3:
>myisamchk --analyze pathtomysqldatadirectory/*/*.MYI
Using MySQL
P
ART II
260
+-------------+--------+---------------+---------+---------+---------------------+------+-------------------------+
| table | type | possible_keys | key | key_len | ref ________________| rows | Extra |
+-------------+--------+---------------+---------+---------+---------------------+------+-------------------------+
| books | ALL | PRIMARY | NULL | NULL | NULL | 4 | where used |
| order_items | index | PRIMARY | PRIMARY | 17 | NULL | 5 | where used; Using index |
| orders | eq_ref | PRIMARY | PRIMARY | 4 | order_items.orderid | 1 | |
| customers | eq_ref | PRIMARY | PRIMARY | 4 | orders.customerid | 1 | |
+-------------+--------+---------------+---------+---------+---------------------+------+-------------------------+
FIGURE 11.3
This is the output of the EXPLAIN after running myisamchk.
You ’ll notice that the way the query is evaluated has changed quite a lot. We’re now only
using all the rows in one of the tables (books), which is fine. In particular, we’re now using
eq_ref for two of the tables and index for the other. MySQL is also now using the whole key
for order_items (17 characters as opposed to 4 previously).
You ’ll also notice the number of rows being used has actually gone up. This is probably caused
by the fact that we have little data in the actual database at this point. Remember that the num-
ber of rows listed is only an estimate—try performing the actual query and checking this. If
these numbers are way off, the MySQL manual suggests using a straight join and listing the
tables in your FROM clause in a different order.
Third, you might want to consider adding a new index to the table. If this query is a) slow, and
b) common, you should seriously consider this. If it’s a one-off query that you’ll never use
again, such as an obscure report requested once, it won’t be worth the effort, as it will slow
other things down. We’ll look at how to do this in the next section.
14 7842 CH11 3/6/01 3:35 PM Page 260