[rt-devel] Slow Queries part3, Groups

[Robert Spier]

> > I wonder why Mysql isn't picking them up.  If you get a chance to
> > create the Domain index, or maybe (Domain,Instance) and run an EXPLAIN
> > on it,
> yep I tried that one, it doesn't get picked up either :(
> Anyone else have ideas, this is really going to cause issues, as
> it means when clicking on new ticket, it takes quite awhile (about 30-60 
> seconds) for the ticket window to load.

Hey, I'm not done yet.

I've been reading the nitty-gritty parts of the MySQL documentation.

When I run the aforementioned query against my database (20k tickets,
10,000 users), it takes about a second.  That's not great, but when
the cache kicks in, it drops down to half a second.  That's still not
great. 

Do you know what is triggering the query?

My next line of research is into MySQL's IGNORE INDEX, FORCE INDEX,
and USE INDEX modifiers.

The one that seems most useful to us FORCE INDEX, which will change the table scan (type=ALL) to a type=range.

+--------------+--------+-----------------------------------------+---------+---------+---------+-------+-----------------------------------------------------------+
| table        | type   | possible_keys                           | key     | key_len | ref     | rows  | Extra                                                     |
+--------------+--------+-----------------------------------------+---------+---------+---------+-------+-----------------------------------------------------------+
| ACL_2        | range  | ACL1                                    | ACL1    |      54 | NULL    |    12 | Using where; Using index; Using temporary; Using filesort |
| main         | ALL    | PRIMARY,Groups1,Groups2,Groups3,Groups4 | NULL    |    NULL | NULL    | 93406 | Using where                                               |
| Principals_1 | eq_ref | PRIMARY                                 | PRIMARY |       4 | main.id |     1 | Using where; Using index; Distinct                        |
+--------------+--------+-----------------------------------------+---------+---------+---------+-------+-----------------------------------------------------------+


+--------------+--------+---------------+---------+---------+---------+-------+-----------------------------------------------------------+
| table        | type   | possible_keys | key     | key_len | ref     | rows  | Extra                                                     |
+--------------+--------+---------------+---------+---------+---------+-------+-----------------------------------------------------------+
| ACL_2        | range  | ACL1          | ACL1    |      54 | NULL    |    12 | Using where; Using index; Using temporary; Using filesort |
| main         | range  | Groups3       | Groups3 |      65 | NULL    | 53818 | Using where                                               |
| Principals_1 | eq_ref | PRIMARY       | PRIMARY |       4 | main.id |     1 | Using where; Using index; Distinct                        |
+--------------+--------+---------------+---------+---------+---------+-------+-----------------------------------------------------------+

(So, it's only got to check half the number of rows now.. but thats still a lot.)

I've toyed with these two new indices, but neither seems to do much.

CREATE INDEX Groups3 ON Groups (Domain);
CREATE INDEX Groups4 ON Groups (Domain,id,Instance);

Basically speed doesn't change much with any of the FORCE INDEX,
which means the indices aren't right.  

If I reduce the query to this:

explain SELECT DISTINCT main.* FROM Groups main, Principals
Principals_1, ACL ACL_2 WHERE ((ACL_2.RightName =
'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( (
ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group'
AND ( main.Domain = 'ACLEquivalence') AND main.id = Principals_1.id)
OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 13) ) AND
main.Type = ACL_2.PrincipalType AND main.id = Principals_1.id) )

Then I'm down to 5483 rows and a range search, as opposed to 50000.
(Although that search still takes 0.41 seconds.)  (And doesn't produce
the right answer, obviously.)

I suspect the real problem here is MySQL's optimizer is falling down.
Which means we may have to take an alternate track.  Anyone want to
try MySQL 4.1?  Or (Jesse?) maybe split the OR's up into a few faster
queries?  Another thought is to replace some of the text strings with lookup numbers.. but that'll slow other things down.

Ok.  Enough time with this for tonight.

-R