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

[Scott Muller]

Ian
 
See http://www.mysql.com/newsletter/2003-01/a0000000108.html 
 
http://www.mysql.com/doc/en/Query_Cache_How.html
 
Basically the Mysql book says that the queries must be :-
 
"exactly the same (byte for byte)"
 
so "select * from table" and "SELECT * FROM TABLE" are different. 
 
Scott.
 

-----Original Message-----
From: rt-devel-admin at lists.fsck.com [mailto:rt-devel-admin at lists.fsck.com]
On Behalf Of Iain Price
Sent: Friday, 3 October 2003 8:56 PM
To: Phil Homewood
Cc: rt-devel at lists.fsck.com
Subject: Re: [rt-devel] Slow queries, part 2, custom fields


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/20031007/80d1dc78/attachment.htm