Slow sql on one dataset, fast on others...how to use explain and optimize

[Are Pedersen]

This SQL takes 2 minutes to run on one database, and seconds on another.
The tables has the same indexes, but different number of rows in them.
Slow database:
persontbl2   = 25000 rows
memberstbl   = 196000 rows
groupchildrentbl   = 9000 rows
structuretreetbl   = 58000 rows
structureacl   = 8800 rows
Fast database:
persontbl2   = 43000 rows
memberstbl   = 128000 rows
groupchildrentbl   = 5200 rows
structuretreetbl   = 28900 rows
structureacl   = 4900 rows
The explain for the slow database is:

SQL-query:
EXPLAIN
SELECT DISTINCT pers.id, pers.firstname
FROM PersonTbl2 pers, GroupChildrenTbl gg1,
     StructureTreeTbl outree, MembersTbl memb1,
     GroupChildrenTbl gg2, MembersTbl memb2, StructureACL sacl
WHERE
memb2.personid = 440287252 AND
memb2.groupid = gg2.gchildid AND
gg2.groupid = sacl.groupid AND
sacl.group_access >= 100 AND
sacl.structid = outree.orgid AND
outree.orgchild = gg1.groupid AND
gg1.gchildid = memb1.groupid AND
memb1.personid = pers.id AND
pers.user_type = 3
GROUP BY
pers.id, pers.firstname
ORDER BY pers.firstname;
 table           type    possible_keys          key      key_len         ref          
rows       Extra
memb2   ref     PRIMARY,groupid,        persgrp         4       const           9      
 Using where; Using index; Using temporary; Using filesort
>                       personid,persgrp
gg2 	ref 	PRIMARY,gchildid,groupid 	gchildid 	4 	memb2.groupid 	1 	 
sacl 	ref 	PRIMARY,groupid,structid, 	groupid 	4 	gg2.groupid 	1 	Using where
>                       group_access
outree 	ref 	PRIMARY,orgid, 	        orgid 	        4 	sacl.structid 	7 	 
>                       orgchild,prim_orgtree_ix2
gg1 	ref 	PRIMARY,gchildid,groupid 	groupid 	4 	outree.orgchild 1 	 
memb1 	ref 	PRIMARY,groupid, 	groupid 	4 	gg1.gchildid 	6 	 
>                       personid,persgrp
pers 	eq_ref 	PRIMARY,usertypeIX 	        PRIMARY 	4 	memb1.personid   1 	Using where


The explain for the fast database is exactly the same, but the rows 
number is now: 3,1,1,6,1,3,1 instead.

Why do these queries run so differently?
We have over 100 databases that run this query fast, all with different 
rowcounts in the tables. But in one database it runs slow...

-----------------------------------------
Are Pedersen
Development Team Leader
Server Operations manager


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]