MySQL + Drupal: Index ignored in INNER JOIN

Question

Our environment: Drupal+MySQL

Examining the query log indicates that the following query, originating from Drupal core's node_load function is taking considerable amount of time.

EXPLAIN on the node_load query reveals that the index is not used on the USER table.

mysql> explain SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, 
    n.comment, n.promote, n.sticky, r.timestamp AS revision_timestamp, r.title, 
    r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data 
FROM xyz_node n 
INNER JOIN xyz_users u ON n.uid = u.uid 
INNER JOIN xyz_node_revisions r ON r.vid = n.vid;

+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
|  1 | SIMPLE      | u     | ALL    | PRIMARY       | NULL    | NULL    | NULL         |  181 |             | 
|  1 | SIMPLE      | n     | ref    | vid,uid       | uid     | 4       | xyz.u.uid |    9 | Using where | 
|  1 | SIMPLE      | r     | eq_ref | PRIMARY       | PRIMARY | 4       | xyz.n.vid |    1 |             | 
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+

Any idea what could be going on, and how i could force MYSQL to use Index on this query?

Answer 1

Tables are not necessarily joined in the order that they are specified in the FROM clause. In this case, it looks like MySQL has decided that in the absence of a WHERE clause in the query, it's probably fastest to scan the users table first, and then join that with the other tables.

The first thing I would do would be to run ANALYZE TABLE on all three tables involved in the query. This updates table statistics and stored key distributions, and allows the join optimizer to make better decisions. Run the EXPLAIN statement afterwards and see if it has changed.

If it hasn't changed, you might need to resort to using the STRAIGHT_JOIN keyword. This forces the join optimizer to join the tables in the exact order specified in the query. To help determine if you should do this, you're supposed to take the product of all the rows values from an EXPLAIN result, and compare it to the actual number of rows returned from the query. So in this case, compare 1629 (181x9x1) to the actual number of rows. If they are significantly different, a STRAIGHT_JOIN might be called for (used as a keyword to SELECT, ie. SELECT STRAIGHT_JOIN n.nid... etc).

As an aside, there is a way to tell MySQL to use a specific index, but I don't think it would work for your user table in this query as it is right now, since there is no WHERE clause. If you end up using STRAIGHT_JOIN, you might possibly need it, but in that case MySQL would likely pick up on the primary key if the user table was not the first table in the join.

You should have a look at the EXPLAIN syntax page for more helpful details on this as well.

This query doesn't look like it should be that slow for what it is. Without a where clause, you can expect a full-table scan somewhere, and MySQL has kept it down to about 1700 total rows examined. It seems like this would only be a problem if it was a high-use query, in which case you might want to examine the underlying architecture that (without a WHERE clause) involves running a query which will affect every user in the system, and will only get heavier as more users are added.

Answer 2

Since MySQL can only use one index per table per query than you can sometimes get a "free" range index scan by just throwing in this seemingly useless condition.

WHERE u.uid > 0

Try adding that clause in, likely the ALL table scan will change to a "range", which is better than a full table scan.

Answer 3

Could this help?

In table node, create an index on (uid,vid) ALTER TABLE 'xyz_node' ADD INDEX user_ver ( 'uid,'vid' ) (Note the 2nd line in the explain output.. under heading possible keys.. you see vid,uid )