Shouldn't the explain statement tell me what's going on...it says 9,1,1,7,1,6,1 on rows, and multiplied up its not much. 378 rows to examine should run fast.
mos wrote:
At 04:21 AM 2/14/2004, you wrote:
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.
It could be you have deleted a lot of rows from the table and therefore it is fragmented. You may want to try optimizing the table using "OPTIMIZE tablename". See http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html
Also your keys may not be evenly distributed. Take a look at "Analyze tablename". http://www.mysql.com/doc/en/ANALYZE_TABLE.html
Mike
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]
-- ----------------------------------------- Are Pedersen Development Team Leader Server Operations manager ([EMAIL PROTECTED])
Tel: +47 24 14 99 61
http://fronter.com Kongensgate 24 N-0153 OSLO, Norway Tel.: +47 24 14 99 99 Fax.: +47 24 14 99 98 -----------------------------------------
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
