On Wed, Dec 18, 2013 at 12:29:05PM -0500, Adam Hobaugh wrote: > >The slow query logs are most helpful when you then run EXPLAIN on the > >query so you know why the database is having trouble. > Here is the EXPLAIN for the two queries. > > mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL > ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) > JOIN CachedGroupMembers CachedGroupMembers_2 ON ( > CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN > CachedGroupMembers CachedGroupMembers_4 ON ( > CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE > ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR > (ACL_3.ObjectType = 'RT::System') OR (ACL_3.ObjectType = 'RT::Queue' > AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System')) AND > (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND > (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' > OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled > = '0') AND (CachedGroupMembers_2.GroupId = '4') AND > (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = > '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id > != '1') ORDER BY main.Name ASC; > +----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref > | rows | Extra | > +----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+ > | 1 | SIMPLE | CachedGroupMembers_2 | range | > DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem > | 10 | NULL | 264 | Using > where; Using index; Using temporary; Using filesort | > | 1 | SIMPLE | main | eq_ref | PRIMARY > | PRIMARY | 4 | > rt4_2_1.CachedGroupMembers_2.MemberId | 1 | > | > | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY > | PRIMARY | 4 | rt4_2_1.main.id > | 1 | Using where; Distinct | > | 1 | SIMPLE | CachedGroupMembers_4 | ref | > DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | > CachedGroupMembers3 | 5 | > rt4_2_1.CachedGroupMembers_2.MemberId | 1 | Using where; Distinct > | > | 1 | SIMPLE | ACL_3 | range | ACL1 > | ACL1 | 85 | NULL > | 13 | Using where; Using index; Distinct | > +----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+ > 5 rows in set (0.01 sec)
Your query here says that MySQL is looking at a pretty tiny dataset and running the same query on multiple databases here shows a wildly different explain and very quick results. This usually points to you having some enormous tables (although the explain doesn't indicate that) or a badly tuned mysql. Can you download and run http://mysqltuner.pl on your database. It would also be interesting to see SELECT TABLE_ROWS, TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'rt4'; changing your database name where needed. -kevin
pgpwhbzre2ZKc.pgp
Description: PGP signature
