I have a bit of code in a page that calls the LimitCustomField sub to
help in collecting statistics on the number of tickets that have each value
set for the Various single select CustomFields for the queue. I pass the
queue in and get a list of CustomFields and then Limit to the que. I
have an outer loop that loops though the custom fields and when if
matches the passed in CF name it then loops through the values and
limits the queue for each keyword 1 by 1 and tracks the number of
tickets left for each keyword. When I make a call the the following function
$tix->LimitCustomField( CUSTOMFIELD => $custom_field->Id , OPERATOR => “=” , VALUE => $value->Name );
It seems to generate a query like the following which takes a looooong time to
return
explain SELECT DISTINCT main.* FROM ( Tickets main LEFT JOIN
TicketCustomFieldValues as TicketCustomFieldValues_1 ON
((TicketCustomFieldValues_1.CustomField = ‘5’)) AND ( main.id = \ \ \
TicketCustomFieldValues_1.Ticket)) WHERE ((main.EffectiveId = main.id));
This query pegs out mysqld to completely chew up one of the 2 procs.
Mysql is version 4.0.12 installed from the ports directory on FreeBSD.
Everything else in the system seems to run fine so I’m assuming I’m
doing this ineffciently and am looking for some help.
Here is the output from explain —
| table | type | possible_keys | key | key_len | ref | rows | Extra |
| main | ALL | NULL | NULL | NULL | NULL | 1828 | Using where; Using temporary |
| TicketCustomFieldValues_1 | ALL | NULL | NULL | NULL | NULL | 12317 | Distinct |
and if I understand the mysql docs correctly a type ALL means this query
ends will have to scan the 12317 rows in the second table 1828 times. I
can see this taking a bit longer. Should I be using a different function
to limit a queue based on a keyword value ? Is there anything I can do
to help speed up this query ? I don’t have any of the other
optimisations turned on yet since all the rest of RT seems to be
performing fine. Any suggestions or further information would be greatly
helpful
I just want your half