Closed (won't fix)
Project:
Drupal core
Version:
6.x-dev
Component:
block.module
Priority:
Minor
Category:
Bug report
Assigned:
Unassigned
Issue tags:
Reporter:
Created:
9 Feb 2009 at 15:31 UTC
Updated:
8 May 2013 at 14:58 UTC
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
Comment #1
dww@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.Comment #2
gábor hojtsyComment #3
david straussIf 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.
Comment #4
multiplextor commentedClosed. The reason: expired.
Comment #4.0
multiplextor commentedadded code tags so you can read WTF this says