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

[Robert Spier]

Ian, 

    On the side... can you convince your MUA to not send text/html to
    the list?  Thanks.

At Mon, 06 Oct 2003 10:45:53 +0100,
Iain Price wrote:
> Yes there are at least some indexes on tables according to show indexes;
> I get 11 rows from "show indexes from Tickets" and one from
> TicketCustomFieldValues... dunno if that corresponds to 11 indexes
> tho :)

Thats the same metric I used.  What version of MySQL are you using?
EXPLAIN wasn't showing all the indices it should have been.

> went from 3 min 51 seconds (hmm thats gone up from 3 min 12 somewhere)... to 0.38
> seconds :O

Not bad at all.

> Did i miss something out?  Should i have these indexes already - did
> i miss install? 

No.  These weren't in the schema.

> or is the default RT DB schema just sadly lacking in essential
> indexes?  Might it be worth getting someone who is good with indexes
> to review the DB?  Personally i just make random indexes on tables
> until my slow query gets better, but this clearly isn't a good
> approach for designing, just (blindly/ignorantly) optimizing
> later...  It does seem (empirically) that a few well placed indexes
> within RT could do absolute wonders for its performance...

Well... at this point RT has 95% of the indexes it neeeds.  I've
created a ticket (#3776) which adds the two from before, and suggests
another three that looked missing to me.  We're at the point where (I
think) all the obvious indexes (will be) in place, and it makes more
sense to add new ones as slow things are found.  

(To anyone:) If you have upgraded from an earlier verison of RT3, you
may want to extract all the CREATE INDEX commands from schema.Mysql
and run them.  Mysql won't let you create duplicate indexes.

> Congratulations are in order, well nailed ;)

You're welcome.


-R