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

[Iain Price]

Robert Spier wrote:

>>+---------------------------+------+---------------+------+---------+------+------+----------------------------------------------+
>>| 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?
>
>  
>

Yes there are at least some indexes on tables according to show indexes;

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

I get 11 rows from "show indexes from Tickets" and one from 
TicketCustomFieldValues... dunno if that corresponds to 11 indexes tho :)

>(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.
>
>  
>
went from 3 min 51 seconds (hmm thats gone up from 3 min 12 
somewhere)... to 0.38 seconds :O

This seems to be the second time that adding indexes to RT's databases 
has reduced the search time by orders of magnitude in excess of a 
thousand times faster (see my original posts regarding the multiple 
requestor search).... 

Did i miss something out?  Should i have these indexes already - did i 
miss install?  or is the default RT DB schema just sadly lacking in 
essential indexes?  Might it be worth getting someone who is good with 
indexes to review the DB?  Personally i just make random indexes on 
tables until my slow query gets better, but this clearly isn't a good 
approach for designing, just (blindly/ignorantly) optimizing later...  
It does seem (empirically) that a few well placed indexes within RT 
could do absolute wonders for its performance...

>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. 
>  
>

Takes just as long without any % and LIKE at all using an = ... (with or 
without indexes)

Congratulations are in order, well nailed ;)

Thanks,
Iain

(with that and the mod_perl 1/2 attachment issue solved, maybe i can go 
back to doing my real job lol)
oh wait, i have one more issue, attachments getting lost being mailed to 
rt - even with mod_perl 1 - i *suspect* this is a timeout in the 
rt-mailgate HTTP connection as i saw that a few times during testing, 
but ATM I work for two departments (and get paid for one naturally) and 
i have to do work for the other department this week so this will have 
to wait :)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20031006/5eb92407/attachment.htm