mysql> explain SELECT COUNT(*) FROM node n WHERE language != '';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | n     | ALL  | NULL          | NULL | NULL    | NULL | 3901948 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> alter table node add index language_idx (language);
Query OK, 3901948 rows affected (32 min 11.43 sec)
Records: 3901948  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(*) FROM node n WHERE language != '';
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | n     | index | language_idx  | language_idx | 38      | NULL | 3901948 | Using where; Using index | 
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
CommentFileSizeAuthor
#6 node-language-index.patch695 bytesrobertdouglass
#3 node-language-index.patch722 bytesrobertdouglass

Comments

robertdouglass’s picture

robertdouglass commented
Status: Active » Closed (duplicate)

Yes, I filed the same issue twice in one week. http://drupal.org/node/375064

robertdouglass’s picture

robertdouglass commented
Version: 6.x-dev » 7.x-dev
Status: Closed (duplicate) » Active

This issue has the clearer title.

robertdouglass’s picture

robertdouglass commented
Status: Active » Needs review
StatusFileSize
new node-language-index.patch722 bytes

I assume we'll follow the "commit to D7 and backport to D6 when we can deal with duplicate indexes" strategy.

damien tournoud’s picture

damien tournoud commented
Status: Needs review » Needs work

This is pretty straightforward, lets just make a patch for D6 and apply on both branches at the same time. Could you please reroll using a _6xxx update function?

damien tournoud’s picture

damien tournoud commented

The node overview filter is generated this way:

      case 'status':
        // Note: no exploitable hole as $key/$value have already been checked when submitted
        list($key, $value) = explode('-', $value, 2);
        $where[] = 'n.' . $key . ' = %d';
        break;
      case 'term':
        $table = "tn$index";
        $where[] = "$table.tid = %d";
        $join .= "INNER JOIN {taxonomy_term_node} $table ON n.nid = $table.nid ";
        break;
      case 'type':
        $where[] = "n.type = '%s'";
        break;
      case 'language':
        $where[] = "n.language = '%s'";
        break;

And $key in status can be promote, status and sticky.

How could we optimize all those different queries?

robertdouglass’s picture

robertdouglass commented
StatusFileSize
new node-language-index.patch695 bytes

Damien, based on the other index patch recently I was under the impression that we're waiting on a db_index_exists() strategy: http://drupal.org/node/363262#comment-1219299

Here's a D6 patch.

robertdouglass’s picture

robertdouglass commented
Status: Needs work » Needs review
robertdouglass’s picture

robertdouglass commented
Status: Needs review » Closed (duplicate)

I combined this fix with another index that affects the same page over here: http://drupal.org/node/373897#comment-1260644

Thus marking as duplicate. Damien, we still need to address your question from http://drupal.org/node/374035#comment-1260563.

damien tournoud’s picture

damien tournoud commented
Status: Closed (duplicate) » Needs review

@Robert: for D7, we have come up with the "upgrades from a development version to another are not supported" rule just because we want to be able to refactor and clean-up update functions at any time.

The upgrade path from D5 to D6 is horribly slow because we haven't done this previously, and as a result, Drupal a few times create an index in an update function just to drop some update functions later.

damien tournoud’s picture

damien tournoud commented
Status: Needs review » Closed (duplicate)

Cross posted with Robert.

samircej’s picture

samircej commented
Status: Closed (duplicate) » Needs review

#3: node-language-index.patch queued for re-testing.

amateescu’s picture

amateescu commented
Status: Needs review » Closed (duplicate)

Why the re-test?