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

[Iain Price]

Phil Homewood wrote:

>Iain Price wrote:
>  
>
>>Table types, hmm gosh i'll have to work out how to do that - the db was 
>>built by the install script for rt 3.0.2
>>    
>>
>
>mysqlshow -i rt3
>
>  
>
>>Dunno if this is right, but a mysqldump rt3 dumps create statements 
>>ending in Type=MyISAM... i dont really know anything about table types 
>>in mysql tho, i'd not even considered this :)  should i be changing 
>>something? :) (how? lol)
>>    
>>
>
>alter table <foo> type=innodb
>(from memory)
>
>however if you have MyISAM tables created by the RT3 install script,
>your mysqld probably doesn't have InnoDB table support. :-( Check
>your my.cnf to make sure it's enabled....
>  
>
Well that confirms its myisam tables then :P  The my.cnf file is pretty 
empty on my redhat 9 box containing just a few directory locations 
mainly and file locations.
Joy of having a dev box tho, i can just try the commands and stuff the 
consequences :)
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 
first.  So certainly seems to make a notable difference, and i'll 
probably go forth and gleefully turn all sorts of databases into innodb 
format in the future now (any reason i shouldn't? :P) but again although 
this is a significant saving, i guess i'm looking for reducing this 
search down to the order of a second or two, 3 max i'd say for what is 
effectively such a simple 'request'  so i guess I still need to make it 
60 times faster still...

I'm going to do some quick (well, slow i guess) poking to see what 
happens if i search for 2 values on the same custom field.  Or values 
from 2 custom fields.  If it takes more than 10 min on a query i'll 
terminate it and log it as a 'time out'.

Thanks for the info, any further suggestions from anyone appreciated :)
Iain
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20031002/d2f6548e/attachment.html