[rt-devel] rt3.0.0: INDEX proposal

[Carlos Canau]

Hi,

rt-3.0.0, MySQL 4.0.12

from my slow logs, I propose the following INDEX:


mysql> explain SELECT DISTINCT main.* FROM Tickets main, Links Links_1   WHERE ((main.EffectiveId = main.id)) AND ( (  ( (Links_1.Type = 'DependsOn')AND(Links_1.LocalBase = '9386'     ) )  ) AND ( (main.Status = 'new')OR(main.Status = 'open')OR(main.Status = 'stalled') ) )  AND main.id = Links_1.LocalTarget;
+---------+--------+---------------------------+---------+---------+---------------------+------+------------------------------+
| table   | type   | possible_keys             | key     | key_len | ref                 | rows | Extra                        |
+---------+--------+---------------------------+---------+---------+---------------------+------+------------------------------+
| Links_1 | ALL    | NULL                      | NULL    |    NULL | NULL                |   21 | Using where; Using temporary |
| main    | eq_ref | PRIMARY,Tickets4,Tickets5 | PRIMARY |       4 | Links_1.LocalTarget |    1 | Using where                  |
+---------+--------+---------------------------+---------+---------+---------------------+------+------------------------------+
2 rows in set (0.00 sec)

mysql> alter table Links add INDEX TypeLocalBaseLocalTarget(Type,LocalBase,LocalTarget);
Query OK, 21 rows affected (0.21 sec)                                                                                                                                            Records: 21  Duplicates: 0  Warnings: 0

mysql> explain SELECT DISTINCT main.* FROM Tickets main, Links Links_1   WHERE ((main.EffectiveId = main.id)) AND ( (  ( (Links_1.Type = 'DependsOn')AND(Links_1.LocalBase = '9386'     ) )  ) AND ( (main.Status = 'new')OR(main.Status = 'open')OR(main.Status = 'stalled') ) )  AND main.id = Links_1.LocalTarget;
+---------+--------+---------------------------+--------------------------+---------+---------------------+------+-------------------------------------------+
| table   | type   | possible_keys             | key                      | key_len | ref                 | rows | Extra                                     |
+---------+--------+---------------------------+--------------------------+---------+---------------------+------+-------------------------------------------+
| Links_1 | ref    | TypeLocalBaseLocalTarget  | TypeLocalBaseLocalTarget |      24 | const,const         |    1 | Using where; Using index; Using temporary |
| main    | eq_ref | PRIMARY,Tickets4,Tickets5 | PRIMARY                  |       4 | Links_1.LocalTarget |    1 | Using where                               |
+---------+--------+---------------------------+--------------------------+---------+---------------------+------+-------------------------------------------+
2 rows in set (0.00 sec)


Best regards,
</Canau