MySQL Forums
Forum List  »  Performance

6-table join, good indices, bad perf
Posted by: Jaap Vermeer
Date: November 16, 2012 11:32AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
6-table join, good indices, bad perf
2588
November 16, 2012 11:32AM
1480
November 17, 2012 10:41AM
2785
November 17, 2012 11:36AM
1245
November 18, 2012 03:37PM
1003
November 18, 2012 05:11PM


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.