6-table join, good indices, bad perf
This query:
SELECT SQL_NO_CACHE g.gc, g.gd, v.gc FROM GroupStructure gs
INNER JOIN Groups g
ON g.gc=gs.sgc AND gs.gc=19 AND g.gl=6
LEFT JOIN (
VCG2LU v
INNER JOIN SegmentsVCG2LU s
INNER JOIN Subjects su
INNER JOIN VehicleContexts vc
)
ON (
v.gc=g.gc AND s.vcc=v.vcc AND su.luc=v.luc AND vc.vcc=s.vcc AND s.vd="OMEGA-B" AND vc.ec=45
)
GROUP BY g.gc ORDER BY g.gd;
With this explain:
mysql> EXPLAIN SELECT g.gc, g.gd, v.gc FROM GroupStructure gs INNER JOIN Groups g ON g.gc=gs.sgc AND gs.gc=19 AND g.gl=6 LEFT JOIN (VCG2LU v INNER JOIN SegmentsVCG2LU s INNER JOIN Subjects su INNER JOIN VehicleContexts vc) ON (v.gc=g.gc AND s.vcc=v.vcc AND su.luc=v.luc AND vc.vcc=s.vcc AND s.vd="OMEGA-B" AND vc.ec=45) GROUP BY g.gc ORDER BY g.gd;
+----+-------------+-------+------+---------------+---------+---------+-------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | gs | ref | gsgc | gsgc | 3 | const | 13 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | g | ref | ggcgl | ggcgl | 5 | opeltis.gs.sgc,const | 1 | Using where |
| 1 | SIMPLE | vc | ref | ec1 | ec1 | 2 | const | 10 | |
| 1 | SIMPLE | v | ref | vccgc | vccgc | 6 | opeltis.vc.vcc,opeltis.gs.sgc | 5 | |
| 1 | SIMPLE | su | ref | sluclul | sluclul | 4 | opeltis.v.luc | 1 | Using index |
| 1 | SIMPLE | s | ref | svd | svd | 27 | const | 10 | |
+----+-------------+-------+------+---------------+---------+---------+-------------------------------+------+----------------------------------------------+
Still takes about 20 seconds to run.
Is there any way i can make it faster?
Subject
Views
Written By
Posted
6-table join, good indices, bad perf
2607
November 16, 2012 11:32AM
1492
November 17, 2012 10:41AM
2802
November 17, 2012 11:36AM
1253
November 18, 2012 03:37PM
1018
November 18, 2012 05:11PM
1138
November 19, 2012 07:22AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.