[Rt-devel] Re: slowness around 3.4.1 and custom fields?

[seph]

Of course, here's another slow query that appears unhelped by those indexes:

SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2  WHERE ((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (Attachments_2.Content LIKE '%customer%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) OR(main.Subject LIKE '%customer%'));

explain says this:

+----------------+------+------------------------------------+---------------+---------+-------+-------+--------------------------+
| table          | type | possible_keys                      | key           | key_len | ref   | rows  | Extra                    |
+----------------+------+------------------------------------+---------------+---------+-------+-------+--------------------------+
| main           | ref  | PRIMARY,Tickets4,Tickets5,Tickets7 | Tickets7      |      17 | const |  1921 | Using where              |
| Transactions_1 | ref  | PRIMARY,Transactions1              | Transactions1 |      64 | const | 21362 | Using where; Using index |
| Attachments_2  | ALL  | Attachments2                       | NULL          |    NULL | NULL  | 27442 | Using where              |
+----------------+------+------------------------------------+---------------+---------+-------+-------+--------------------------+

the slow logs say it took 30 minutes to run.

seph