Missing index in system table [#371504]

mysql> explain  SELECT filename FROM system WHERE name = 'cvs' AND type = 'module';
+----+-------------+--------+------+-------------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys     | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+-------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | system | ref  | modules,bootstrap | modules | 38      | const |   63 | Using where | 
+----+-------------+--------+------+-------------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table system add index name (name);
Query OK, 127 rows affected (0.06 sec)
Records: 127  Duplicates: 0  Warnings: 0

mysql> explain  SELECT filename FROM system WHERE name = 'cvs' AND type = 'module';
+----+-------------+--------+------+------------------------+------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys          | key  | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+------------------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | system | ref  | modules,bootstrap,name | name | 767     | const |    1 | Using where | 
+----+-------------+--------+------+------------------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Comments

Comment #1

dave reid

he/him

English

Nebraska USA

commented 9 February 2009 at 15:43

Status:

Active

» Closed (duplicate)

See #215080: Performance: change {system}.type: alter table system modify column type VARCHAR(32); that should be backported to 6.x.

Comment #2

robertdouglass commented 18 February 2009 at 16:54

Status:

Closed (duplicate)

» Active

So wait? Gábor says he doesn't want to change the schema in D6. Perhaps this index is a good idea in addition to #215080: Performance: change {system}.type: alter table system modify column type VARCHAR(32);?

Comment #3

dave reid

he/him

English

Nebraska USA

commented 19 February 2009 at 03:06

I posted the index backport (without the schema change in #215080: Performance: change {system}.type: alter table system modify column type VARCHAR(32);.

Comment #4

keith.smith commented 19 February 2009 at 04:07

Title:

Missign index in system table

» Missing index in system table

Comment #5

david strauss

he/him

commented 20 February 2009 at 05:46

Priority:

Normal

» Minor

The index ought to be on name and type, preferably in that order.

Setting to minor because this is a small, non-volatile table whose queries are mostly handled out of the query cache.

Comment #6

stormsweeper commented 4 June 2009 at 04:12

The indexes help Postgres performance greatly:

drupalpgsql=> explain analyze SELECT filename FROM system WHERE name = 'user' AND type = 'module';
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Seq Scan on "system"  (cost=0.00..2185.16 rows=1 width=57) (actual time=143.113..143.276 rows=1 loops=1)
   Filter: (((name)::text = 'user'::text) AND (("type")::text = 'module'::text))
 Total runtime: 143.415 ms
(3 rows)

drupalbnl=> explain analyze SELECT * FROM system WHERE type = 'theme';
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Seq Scan on "system"  (cost=0.00..2185.81 rows=69 width=434) (actual time=129.436..129.544 rows=10 loops=1)
   Filter: (("type")::text = 'theme'::text)
 Total runtime: 129.701 ms
(3 rows)

drupalpgsql=> create index system_type_name on system (type, name);
CREATE INDEX
drupalpgsql=> explain analyze SELECT filename FROM system WHERE name = 'user' AND type = 'module';
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on "system"  (cost=4.26..8.28 rows=1 width=57) (actual time=0.655..0.660 rows=1 loops=1)
   Recheck Cond: ((("type")::text = 'module'::text) AND ((name)::text = 'user'::text))
   ->  Bitmap Index Scan on system_type_name  (cost=0.00..4.26 rows=1 width=0) (actual time=0.329..0.329 rows=7 loops=1)
         Index Cond: ((("type")::text = 'module'::text) AND ((name)::text = 'user'::text))
 Total runtime: 0.814 ms
(5 rows)
drupalbnl=> explain analyze SELECT * FROM system WHERE type = 'theme';
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using system_type_name on "system"  (cost=0.00..236.83 rows=69 width=434) (actual time=0.446..0.724 rows=10 loops=1)
   Index Cond: (("type")::text = 'theme'::text)
 Total runtime: 0.876 ms
(3 rows)

Note that I'm using the full column name in Postgres, as it supports them. The way Drupal currently implements partial text indexing also doesn't work, but I will file that as another issue.