最小索引::

  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）; 
 

您也可以创建，

  CREATE INDEX e1 ON e（col1）; 

 CREATE INDEX a4 ON a（col1）; 
 

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 <query> 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) ;