[rt-devel] Slow queries, part 2, custom fields

[Robert Spier]

> +---------------------------+------+---------------+------+---------+------+------+----------------------------------------------+
> | main                      | ALL  | NULL          | NULL |    NULL | NULL | 4663 | Using where; Using temporary; Using filesort |
> | TicketCustomFieldValues_1 | ALL  | NULL          | NULL |    NULL | NULL | 8447 | Using where; Distinct                        |
> +---------------------------+------+---------------+------+---------+------+------+----------------------------------------------+
> 2 rows in set (0.04 sec)

This looks so very wrong.  Are you sure you have _any_ indices?


This is the results I get on a fresh installation for this:

SELECT DISTINCT main.* FROM ( Tickets main LEFT JOIN
TicketCustomFieldValues as TicketCustomFieldValues_1  ON
((TicketCustomFieldValues_1.CustomField = '1')) AND (  main.id =
TicketCustomFieldValues_1.Ticket))  WHERE ((main.EffectiveId =
main.id)) AND ((main.Type = 'ticket')) AND ( (  (
(TicketCustomFieldValues_1.Content LIKE '%%xxxxxxxxxx%%') )  ) )
ORDER BY main.id ASC LIMIT 50

It's still not great, but its much improved.

*************************** 1. row ***************************
        table: TicketCustomFieldValues_1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 32962
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
        table: main
         type: eq_ref
possible_keys: PRIMARY,Tickets4,Tickets5
          key: PRIMARY
      key_len: 4
          ref: TicketCustomFieldValues_1.Ticket
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

I have 11 indexes on my Tickets table.  And only one on
TicketCustomFieldValues.

(Jesse, weren't there some patches to put more indexes on TCFV?  It
definitely needs some.)

I don't have time to do actual timings, but:

 This should help this particular search.

 CREATE INDEX TCFV1 ON TicketCustomFieldValues (CustomField,Ticket,Content);

 This should help CF display in general.

 CREATE INDEX TCFV3 ON TicketCustomFieldValues (CustomField,Ticket);

If you could try those, it would be appreciated.

Also, the doubling of the %% *shouldn't* be causing a problem, but you
might want to try with just a single %.  It might let MySQL optimize
better. 

-R