[rt-users] Slow Query w/ LimitCustomField RT 3.0.3Pre1

[Eric Liedtke]

It's seems fuzzy now but I think on Wed, Jun 04, 2003 at 11:18:08PM +0200 , Michael van Elst said:
> On Wed, Jun 04, 2003, Eric Liedtke wrote:
> 
> > 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 at 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.