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
Comment #1
damien tournoud commentedAs a workaround, we can switch to a
LEFT JOINhere.