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

[Iain Price]

Phil Homewood wrote:

>Iain Price wrote:
>  
>
>>OK all my tables are now innodb format... The query now takes 3 min 12 
>>seconds (previously was between 4m30 and 5min).  This will double up to 
>>6 and a half minutes through the RT3 search cos of the count() query 
>>    
>>
>
>Actually, I don't think it should double, if you've enabled mysql's
>query_cache. Though I'm not 100% sure the optimiser will reuse the
>cached result if there's a count() there....
>
>At any rate:- that's still a very slow query. mysql's "explain"
>might give some tips on why it's being so slow.
>  
>
Yeah been chatting to someone else off-list about databases... Now i've 
got proper query caching in place, and a 'large' mysql memory config 
thing, the search still takes 3min 12 but at least the repetition of the 
search should be near instant...... but yeah these approaches aren't 
solving the problem of a badly analysed query.

Also, sadly regarding the 'doubling' of the search

[root at xxxxxxxxxx root]# time mysql rt3 -e "SELECT count(DISTINCT main.id) 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%%') )  ) )" >/dev/null
real    3m4.694s
user    0m0.004s
sys     0m0.002s

[root at xxxxxxxxxx root]# time mysql rt3 -e "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" >/dev/null
real    3m12.915s
user    0m0.006s
sys     0m0.002s

(lol at it taking me a few seconds to realise why user/sys values are 
negligable)

I.e when RT executes the query twice, once for the 'xx results', once to 
get the results, mysql evaluates each one from scratch, even thought the 
where clause on the statements is the same.  I dont know if its the 
aggregation count() function or the sort/limit that causes the results 
to be unusable, or even the different fields searched for (one counts id 
and the other returns *) - this is unfortunate - it would be nice if 
mysql would cache the pre-aggregation result set - or whever causes it 
this issue, but this isn't a RDBMS analysis really  -  not the theory of 
writing one anyway :)

And to prove it all works, when i run it again immediately after...

[root at xxxxxxxxxx root]# time mysql rt3 -e "SELECT count(DISTINCT main.id) 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%%') )  ) )" >/dev/null
real    0m0.007s
user    0m0.004s
sys     0m0.004s

[root at xxxxxxxxxx root]# time mysql rt3 -e "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" >/dev/null
real    0m0.007s
user    0m0.000s
sys     0m0.004s

Once again tests on ms-sql with these queries produces near instant results.


As for explains...


+---------------------------+-------+---------------+-------+---------+------+------+----------------------------------------------+
| table                     | type  | possible_keys | key   | key_len | ref  | rows | Extra                                        |
+---------------------------+-------+---------------+-------+---------+------+------+----------------------------------------------+
| main                      | range | test          | test  |      17 | NULL | 2286 | Using where; Using temporary; Using filesort |
| TicketCustomFieldValues_1 | index | NULL          | test2 |     268 | NULL | 8651 | Using where; Using index; Distinct           |
+---------------------------+-------+---------------+-------+---------+------+------+----------------------------------------------+

2 rows in set (0.00 sec)


oh thats with my testing index in there which made little (30 seconds) 
difference to the search...

+---------------------------+------+---------------+------+---------+------+------+----------------------------------------------+
| table                     | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+---------------------------+------+---------------+------+---------+------+------+----------------------------------------------+
| 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)


without said indexes....  (wow theres less rows without the index :P 
what does that mean lol)  i am half hartedly hacking indexes around 
especially after my last success with indexes but i still dont think 
this will get it down to a reasonable search time (3-5 seconds) :|


As ever any suggestions gratefully received :)

Thanks,
Iain

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20031003/2868d29b/attachment.html