Slow Query w/ LimitCustomField RT 3.0.3Pre1

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

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));

The table TicketCustomFieldValues is not indexed. Creating indexes
on the CustomField and Ticket columns might help.

,eM""=.            a"-.                         Michael van Elst

dWWMWM" - :GM==; mlelstv@dev.de.cw.net
:WWMWMw=–. “W=’ cable & wireless
9WWMm==-.
”-Wmw-" CABLE & WIRELESS

It’s seems fuzzy now but I think on Wed, Jun 04, 2003 at 11:18:08PM +0200 , Michael van Elst said:

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));

The table TicketCustomFieldValues is not indexed. Creating indexes
on the CustomField and Ticket columns might help.

Thanks for helping me fix the problem…as my SQL is a little rough I
enlisted the help of one of our DBA’s to explain what was going on in
the statement. After that talk I began to try each piece of the query
and did some testing with the following statement…

select * from ( Tickets main LEFT JOIN TicketCustomFieldValues as
TicketCustomFieldValues_1 ON ( TicketCustomFieldValues_1.CustomField =
‘5’) AND ( main.id = TicketCustomFieldValues_1.Ticket) )

as this portion of the origninal query took a while on it’s own.
Originally the above statement reported this…

1880 rows in set (1 min 42.02 sec)

I tried the CustomField index and it didn’t seem to help much and
returned this…

1880 rows in set (1 min 38.81 sec)

So I figured I would index the other side of the join and created an
index on Ticket and the query now reports this…

1880 rows in set (0.47 sec)

Amazing what a proper index can do for you ; ) Thanks again for the
help.


,eM""=. a"-. Michael van Elst
dWWMWM" - :GM==; mlelstv@dev.de.cw.net
:WWMWMw=–. “W=’ cable & wireless
9WWMm==-.
”-Wmw-" CABLE & WIRELESS

Act as if the future of the universe depends on what you do,
while laughing at yourself for thinking that your actions
make any difference.