mysql多个left join查询流程,使用多个LEFT JOIN和GROUP BY优化MySQL查询

I have the following query which I use frequently:

SELECT a.col1,

b.col1,

d.col1,

c.col1,

c.col2,

c.col3,

c.col4,

a.col2,

c.col5,

c.col6,

c.col7,

b.col2

FROM a

LEFT JOIN c ON a.col3 = c.col1

LEFT JOIN b ON a.col4 = b.col1

LEFT JOIN d ON b.col3 = d.col2

LEFT JOIN e ON b.col3 = e.col1

where a.col4 != 'temp' and a.col5!=2

GROUP BY a.col1,

b.col1,

d.col1,

c.col1,

c.col3,

c.col4,

a.col2,

c.col5,

c.col6,

c.col7,

b.col2

limit 50;

This query is run on a remote server and it takes about 5 mins (My internet connection is not slow) to show the results. I have only used basic MySQL until now and I am not sure how to optimize the above query. I searched the net for optimizing it, like adding indexes, but I found them only for very simple cases and I am not able extend them to this query.

Can someone please help me create indexes for optimizing the above query or any other method to make the query run faster (like creating an additional temporary table).

a has about 1.3 million records, b - 80k records, c - 150k records, d - 150 records.

Running the query SHOW CREATE TABLE a gives the following result:

| a | CREATE TABLE `a` (

`col1` int(10) unsigned NOT NULL AUTO_INCREMENT,

`col4` int(10) unsigned NOT NULL DEFAULT '0',

`col5` int(10) unsigned NOT NULL DEFAULT '0',

`col6` varchar(100) NOT NULL DEFAULT '',

`col3` int(10) unsigned NOT NULL DEFAULT '0',

`col7` varchar(250) NOT NULL DEFAULT '',

`col2` int(10) unsigned NOT NULL DEFAULT '0',

`col8` mediumtext,

`col9` smallint(6) NOT NULL DEFAULT '0',

`col10` smallint(6) NOT NULL DEFAULT '0',

`col11` varchar(15) NOT NULL DEFAULT '',

`col12` smallint(5) unsigned NOT NULL DEFAULT '0',

`col13` smallint(6) NOT NULL DEFAULT '0',

`col14` smallint(5) unsigned NOT NULL DEFAULT '0',

`col15` smallint(5) unsigned NOT NULL DEFAULT '0',

`col16` int(10) unsigned NOT NULL DEFAULT '0',

`col17` int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`col1`),

KEY `col3` (`col3`),

KEY `col4` (`col4`,`col3`),

KEY `col2` (`col2`),

KEY `col1` (`col1`),

KEY `col1_2` (`col1`),

KEY `col1_3` (`col1`),

KEY `col1_4` (`col1`),

KEY `col1_5` (`col1`),

KEY `col1_6` (`col1`),

KEY `col1_7` (`col1`),

FULLTEXT KEY `col7` (`col7`,`col8`)

) ENGINE=InnoDB AUTO_INCREMENT=1339383 DEFAULT CHARSET=latin1 |

EXPLAIN gives the following result:

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+

| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 149 | Using where; Using temporary; Using filesort |

| 1 | SIMPLE | b | ref | PRIMARY,col3 | col3 | 2 | db.e.col1 | 286 | Using index condition |

| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 2 | db.b.col3 | 1 | NULL |

| 1 | SIMPLE | a | ref | col4 | col4 | 4 | db.b.col1 | 10 | Using where |

| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | db.a.col3 | 1 | NULL |

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+

解决方案

Minimum Indexes ::

CREATE INDEX a0 ON a (col4, col5) ;

CREATE INDEX a1 ON a (col3) ;

CREATE INDEX a2 ON a (col4) ;

CREATE INDEX b1 ON b (col1) ;

CREATE INDEX b2 ON b (col3) ;

CREATE INDEX c1 ON c (col1) ;

CREATE INDEX d1 ON d (col2) ;

You may also create,

CREATE INDEX e1 ON e (col1) ;

CREATE INDEX a4 ON a (col1) ;