[rt-users] TicketHistory slow on 4.2.0

[Tod Detre]

Here are the EXPLAIN results:

mysql> EXPLAIN SELECT count(main.id)    FROM ObjectCustomFieldValues main;
+----+-------------+-------+-------+---------------+--------------------------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key
       | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+--------------------------+---------+------+---------+-------------+
|  1 | SIMPLE      | main  | index | NULL          |
ObjectCustomFieldValues2 | 265     | NULL | 3098664 | Using index |
+----+-------------+-------+-------+---------------+--------------------------+---------+------+---------+-------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT count(main.id) FROM ObjectCustomFieldValues main
 WHERE (main.Disabled = '0') AND (main.ObjectId = '3910406') AND
(main.ObjectType = 'RT::Transaction');
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len |
ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | main  | ALL  | NULL          | NULL | NULL    |
NULL | 3098664 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)


So it looks like the first query is using an index, but the second is
not. I've confirmed that the ObjectCustomFieldValues table has the
correct indexes listed in the schema.mysql. However the disabled field
is not indexed. Should that be added?

mysql> show index from ObjectCustomFieldValues;
+-------------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                   | Non_unique | Key_name                 |
Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ObjectCustomFieldValues |          0 | PRIMARY                  |
        1 | id          | A         |     3195564 |     NULL | NULL
|      | BTREE      |         |               |
| ObjectCustomFieldValues |          1 | ObjectCustomFieldValues1 |
        1 | Content     | A         |     1065188 |     NULL | NULL
| YES  | BTREE      |         |               |
| ObjectCustomFieldValues |          1 | ObjectCustomFieldValues2 |
        1 | CustomField | A         |          17 |     NULL | NULL
|      | BTREE      |         |               |
| ObjectCustomFieldValues |          1 | ObjectCustomFieldValues2 |
        2 | ObjectType  | A         |          17 |     NULL | NULL
|      | BTREE      |         |               |
| ObjectCustomFieldValues |          1 | ObjectCustomFieldValues2 |
        3 | ObjectId    | A         |     3195564 |     NULL | NULL
|      | BTREE      |         |               |
+-------------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.12 sec)

-- 
Tod