We have one really slow query in node_update_7006(), which is repeated at each batch invocation:


EXPLAIN SELECT nr.nid AS nid, nr.vid AS vid, nr.body AS body, nr.teaser AS teaser, nr.format AS format, n.type AS type, n.status AS status, n.comment AS comment, n.promote AS promote, n.sticky AS sticky, n.language AS language FROM  node_revision nr INNER JOIN node n ON n.vid = nr.vid WHERE  (nr.vid > '212108')  ORDER BY nr.vid ASC LIMIT 200 OFFSET 0;
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows   | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------+
|  1 | SIMPLE      | n     | range  | vid           | vid     | 4       | NULL            | 300073 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | nr    | eq_ref | PRIMARY       | PRIMARY | 4       | drupalorg.n.vid |      1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------+

This is because the query optimizer of MySQL is really dumb, and generally starts this type query from the table that has the less cardinality (here, always the node table).

Comments

damien tournoud’s picture

As a workaround, we can switch to a LEFT JOIN here.

Status: Active » Closed (outdated)

Automatically closed because Drupal 7 security and bugfix support has ended as of 5 January 2025. If the issue verifiably applies to later versions, please reopen with details and update the version.