mysql> explain SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.status = 1 AND b.custom != 0 AND (r.rid IN (2,3,4,5,8) OR r.rid IS NULL) ORDER BY b.weight, b.module;
+----+-------------+-------+------+---------------+---------+---------+----------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref                              | rows | Extra                                        |
+----+-------------+-------+------+---------------+---------+---------+----------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL    | NULL    | NULL                             |   61 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | r     | ref  | PRIMARY       | PRIMARY | 292     | drupal6.b.module,drupal6.b.delta |    1 | Using where; Using index; Distinct           | 
+----+-------------+-------+------+---------------+---------+---------+----------------------------------+------+----------------------------------------------+


alter table blocks add key status (status);

explain SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.status = 1 AND b.custom != 0 AND (r.rid IN (2,3,4,5,8) OR r.rid IS NULL) ORDER BY b.weight, b.module;
+----+-------------+-------+------+---------------+---------+---------+----------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref                              | rows | Extra                                        |
+----+-------------+-------+------+---------------+---------+---------+----------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | b     | ref  | status,custom | status  | 1       | const                            |   30 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | r     | ref  | PRIMARY       | PRIMARY | 292     | drupal6.b.module,drupal6.b.delta |    1 | Using where; Using index; Distinct           | 
+----+-------------+-------+------+---------------+---------+---------+----------------------------------+------+----------------------------------------------+

Comments

dww’s picture

@killes: I added <code> tags to this (and a few of your other posts). It's very hard to read this stuff without them. Can you please use those whenever you paste mysql output like this? Thanks.

gábor hojtsy’s picture

Title: index on blocks.status helpful » Missing index for status on blocks
david strauss’s picture

Priority: Normal » Minor

If we can make custom not use "!=", we should put the index on (status, custom).

Marking as minor because it's a small, non-volatile table, and this query is probably being served from the query cache.

multiplextor’s picture

Status: Active » Closed (won't fix)

Closed. The reason: expired.

multiplextor’s picture

Issue summary: View changes

added code tags so you can read WTF this says