Drupal 6 has a few problems with large numbers of nodes.

Slow query problems in search index update (/cron.php)

There are three slow queries in the search index update process that gets run during cron.  Slow query #1, in node_update_index(), picks out the node IDs to (re)index.  Slow query #2, in node_update_index(), recalculates the value of node_cron_comments_scale.  Slow query #3, in search_update_totals(), figures out which rows have been deleted from {search_index} and need to be deleted from {search_total}.

Bug 312393: Slow query in node_update_index()

The Fix (Drupal 6.4):

1. run this MySQL command (replacing d_ with your own Drupal table prefix): ALTER TABLE d_search_dataset ADD KEY type_reindex (type, reindex);
2. Download and install the search_manynodes module (.info and .module files) at the bottom of this page
3. Run this MySQL (replacing d_ with your own Drupal table prefix): INSERT INTO d_search_dataset (sid, type, reindex) SELECT nid AS sid, 'node' AS type, 2147483647 AS reindex FROM d_node;
4. Download and apply the search_manynodes.patch file at the bottom of this page (also applies to Drupal 6.5)

Description of bug 312393:

The query in node_update_index() that picks the list of nodes to (re)index is slow. It appears to have been introduced in http://drupal.org/node/146466 and takes ~54 seconds to run with ~2.4 million nodes.  It is slow for two reasons:

1. the ORDER BY d.reindex is not serviced by an index, forcing a filesort
2. the LEFT JOIN forces a full table scan of the {node} table

mysql> explain SELECT n.nid
   FROM d_node n
   LEFT JOIN d_search_dataset d ON d.type = 'node'
      AND d.sid = n.nid
   WHERE d.sid IS NULL OR d.reindex <> 0
   ORDER BY d.reindex ASC, n.nid ASC LIMIT 0, 100 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: n
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2435443
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
         type: ref
possible_keys: sid_type
          key: sid_type
      key_len: 55
          ref: n.nid,const
         rows: 10
        Extra: Using where

To get rid of the filesort, the query can be split into two separate queries:

explain SELECT n.nid FROM d_node n
   LEFT JOIN d_search_dataset d
      ON d.type = 'node' AND d.sid = n.nid
   WHERE d.sid IS NULL ORDER BY n.nid ASC
   LIMIT 0, 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: n
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2435443
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
         type: ref
possible_keys: sid_type
          key: sid_type
      key_len: 55
          ref: n.nid,const
         rows: 10
        Extra: Using where; Using index; Not exists

and this one, which requires an additional index:

ALTER TABLE d_search_dataset
   ADD KEY type_reindex (type, reindex);
explain SELECT d.sid as nid FROM d_search_dataset d
   WHERE d.type = 'node' AND d.reindex <> 0
   ORDER BY d.reindex ASC LIMIT 0, 100 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
         type: range
possible_keys: type_reindex
          key: type_reindex
      key_len: 55
          ref: NULL
         rows: 841285
        Extra: Using where

These two can be combined into the same query (at least in MySQL):

(SELECT n.nid FROM {node} n LEFT JOIN {search_dataset} d ON d.type = 'node' AND d.sid = n.nid WHERE d.sid IS NULL ORDER BY n.nid ASC LIMIT 0, 100) UNION (SELECT d.sid as nid FROM {search_dataset} d WHERE d.type = 'node' AND d.reindex <> 0 ORDER BY d.reindex ASC LIMIT 0, 100)

In my one test, this reduced the query time from ~54sec to ~33sec

The bulk of the 33 sec is used by the first LEFT JOIN query.  We can remove that too by ensuring that there will never be a case where a node ID exists in {node} and not in {search_dataset}.  See the search_manynodes module at the end of this page for an example of how to do that.  We can then get rid of the LEFT JOIN query entirely, which reduces query time from ~33sec to ~0.04sec.

Bug 336483: Slow query in node_update_index()

The Fix (Drupal 6.6):

1. ALTER TABLE node_comment_statistics ADD INDEX (comment_count);

Description of Bug 336483:

This is simply a query which does not have an index:

explain MAX(comment_count)
FROM d_node_comment_statistics \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d_node_comment_statistics
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2435440
        Extra: 

Adding the index as described above causes the query to be optimized away:

explain MAX(comment_count)
FROM d_node_comment_statistics \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away

Which reduces the query time from ~4.77 sec to 0.00 sec

Bug 312390: Slow query in search_update_totals()

The Fix (Drupal 6.4):

1. Apply 312390-6.4.patch (also applies to Drupal 6.5)

Description of bug:

search_update_totals() keeps the materialized view {search_total} up to date.  It does so in two steps.  Step 1 inserts or updates any total word scores that have changed during the current cron run.  Step 2 looks for rows that should be deleted from {search_total} with this query:


explain SELECT t.word AS realword, i.word
FROM d_search_total t
LEFT JOIN d_search_index i ON t.word = i.word
WHERE i.word IS NULL \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 152
          ref: NULL
         rows: 1249382
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: ref
possible_keys: word_sid_type,word
          key: word
      key_len: 152
          ref: t.word
         rows: 3
        Extra: Using where; Using index; Not exists

which gets expensive as the number of rows in {search_total} grows large.

Bug 312395: Problem in search settings (admin/settings/search)

The fix (Drupal 6.4):

1. Apply the fix for bug 312393 described above
2. Apply 312395-6.4.patch from the bottom of this page (also applies to Drupal 6.5)

Description of problem:

EXPLAIN SELECT COUNT(*) FROM d_node n
LEFT JOIN d_search_dataset d
 ON d.type = 'node' AND d.sid = n.nid
WHERE n.status = 1
AND (d.sid IS NULL OR d.reindex <> 0) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: n
         type: index
possible_keys: node_status_type
          key: node_status_type
      key_len: 106
          ref: NULL
         rows: 1826583
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
         type: ref
possible_keys: sid_type
          key: sid_type
      key_len: 55
          ref: n.nid,const
         rows: 10
        Extra: Using where

Changing the query to only use the {search_dataset}:

EXPLAIN SELECT COUNT(*) FROM d_search_dataset d
WHERE d.type = 'node' AND d.reindex <> 0 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
         type: range
possible_keys: type_reindex
          key: type_reindex
      key_len: 55
          ref: NULL
         rows: 619703
        Extra: Using where; Using index

increased the speed from ~45 sec to ~0.91 sec.

Bug 336358: Slow query in /cron.php ping.module ping_cron()

Note: this patch has been applied to Drupal 6 as of November 6, 2009 and should be included in the next (Drupal 6.15) Drupal 6 release.

The fix (Drupal 6.6):

1. Apply 336358-6.6.patch from the bottom of this page

Description of problem:

The query run in the ping_cron() function does a full table scan on the {node} table:

EXPLAIN SELECT COUNT(*) FROM d_node
WHERE status = 1
AND (created > '1227090316'
  OR changed > '1227090316') \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d_node
         type: ALL
possible_keys: node_created,node_changed,node_status_type
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1826583
        Extra: Using where

Simply splitting the query into two parts lets both individual parts use indexes which already exist:

EXPLAIN SELECT COUNT(*) FROM d_node
WHERE status = 1
AND created > '1227090316' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d_node
         type: range
possible_keys: node_created,node_status_type
          key: node_created
      key_len: 4
          ref: NULL
         rows: 55
        Extra: Using where

EXPLAIN SELECT COUNT(*) FROM d_node
WHERE status = 1
AND changed > '1227090316' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d_node
         type: range
possible_keys: node_changed,node_status_type
          key: node_changed
      key_len: 4
          ref: NULL
         rows: 26
        Extra: Using where

increased the speed from ~8.99 sec to ~(0.00 + 0.00) sec.

Attachment	Size
search_manynodes.info	124 bytes
search_manynodes.module	317 bytes
312395-6.4.patch	655 bytes
312390-6.4.patch	2 KB
search_manynodes.patch	840 bytes
336358-6.6.patch	796 bytes
312390-5.12.patch	2.2 KB