MySQL + Drupal: Index is ignored in INNER JOIN
Our environment: Drupal + MySQL
Examining the request log indicates that the next request coming from the Drupal core node_load function is taking a significant amount of time.
EXPLAIN in the node_load query indicates that no index is being 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 might be happening and how can I get MYSQL to use Index for this query?
a source to share
Tables are not necessarily joined in the order in which they appear in the proposal FROM
. In this case, it seems that MySQL decided that in the absence of a clause WHERE
in the query, it is most likely the fastest to scan the users table and then join other tables.
The first thing I would like to do is run ANALYZE TABLE
on all three tables involved in the query. This updates table statistics and stores key distributions and allows the join optimizer to make better decisions. Run the statement EXPLAIN
and see if it has changed.
If it hasn't changed, you may need to use a keyword STRAIGHT_JOIN
. This forces the join optimizer to join the tables in the exact order specified in the query. To determine if you should do this, you must take the product of all values rows
from the result EXPLAIN
and compare it to the actual number of rows returned from the query. So in this case compare 1629 (181x9x1) with the actual number of lines. If they differ significantly, it can be called STRAIGHT_JOIN
(used as a keyword for SELECT
, i.e. 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 will work your user table in this query as it does now, since there is no suggestion WHERE
. If you end up using STRAIGHT_JOIN
it, you might need it, but in this case MySQL will likely pick up the primary key if the user table was not the first table in the join.
You can also view the EXPLAIN syntax page for more details on this.
This request doesn't look like it should be as slow as it is. Without a where clause, you can expect a full table scan somewhere, and MySQL saved it to about 1,700 rows scanned. It looks like this would only be an issue if it was a high level request, in which case you might need to look into the underlying architecture, which (no suggestion WHERE
) involves running a request that will affect every user on the system, and will only get heavier as more users are added.
a source to share
Since MySQL can only use one index per table for each query, you can sometimes get a "free" range index scan by simply throwing away this seemingly useless condition.
WHERE u.uid > 0
Try adding this sentence, it will probably change the ALL ALL scan to "range", which is better than a full table scan.
a source to share