CODEWITHSUNDEEP
mysqlquery-optimization
simplfuzz
simplfuzz

Reputation: 12925

Mysql Slow Query - Even with all the indices

mysql> explain
    select c.userEmail,f.customerId 
    from comments c 
      inner join flows f 
        on (f.id = c.typeId) 
      inner join users u 
        on (u.email = c.userEmail) 
    where c.addTime >= 1372617000 
      and c.addTime <= 1374776940 
      and c.type = 'flow' 
      and c.automated = 0;
+----+-------------+-------+--------+----------------------------------------+------------+---------+---------------------+--------+-------------+
| id | select_type | table | type   | possible_keys                          | key        | key_len | ref                 | rows   | Extra       |
+----+-------------+-------+--------+----------------------------------------+------------+---------+---------------------+--------+-------------+
|  1 | SIMPLE      | f     | index  | PRIMARY                                | customerId | 4       | NULL                | 144443 | Using index |
|  1 | SIMPLE      | c     | ref    | userEmail_idx,addTime,automated,typeId | typeId     | 198     | f.id,const  |      1 | Using where |
|  1 | SIMPLE      | u     | eq_ref | email                                  | email      | 386     | c.userEmail |      1 | Using index |
+----+-------------+-------+--------+----------------------------------------+------------+---------+---------------------+--------+-------------+

How do I make the above query faster - it constantly shows up in the slow query logs.
Indexes present :

  1. id is the auto incremented primary key of the flows table.
  2. customerId of flows table.
  3. userEmail of comments table.
  4. composite index (typeId,type) on comments table.
  5. email of users table (unique)
  6. automated of comments table.
  7. addTime of comments table.

Number of rows :
1. flows - 150k
2. comments - 500k (half of them have automated = 1 and others have automated = 0) (also value of type is 'flow' for all the rows except 500)
3. users - 50

Table schemas :

 users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 

 comments | CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userEmail` varchar(128) DEFAULT NULL,
  `content` mediumtext NOT NULL,
  `addTime` int(11) NOT NULL,
  `typeId` int(11) NOT NULL,
  `automated` tinyint(4) NOT NULL,
  `type` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userEmail_idx` (`userEmail`),
  KEY `addTime` (`addTime`),
  KEY `automated` (`automated`),
  KEY `typeId` (`typeId`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=572410 DEFAULT CHARSET=utf8 |


 flows | CREATE TABLE `flows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(32) NOT NULL,
  `status` varchar(128) NOT NULL,
  `customerId` int(11) NOT NULL,
  `createTime` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `flowType_idx` (`type`),
  KEY `customerId` (`customerId`),
  KEY `status` (`status`),
  KEY `createTime` (`createTime`),
) ENGINE=InnoDB AUTO_INCREMENT=134127 DEFAULT CHARSET=utf8 |

Upvotes: 3

Views: 208

Answers (1)

user1914530
user1914530

Reputation:

You have the required indexes to perform the joins efficiently. However, it looks like MySQL is joining the tables in a less efficient manner. The EXPLAIN output shows that it is doing a full index scan of the flows table then joining the comments table.

It will probably be more efficient to read the comments table first before joining. That is, in the order you have specified in your query so that the comment set is restricted by the predicates you have supplied (probably what you intended).

Running OPTIMISE TABLE or ANALYZE TABLE can improve the decision that the query optimiser makes. Particularly on tables that have had extensive changes.

If the query optimiser still gets it wrong you can force tables to be read in the order you specify in the query by beginning your statement with SELECT STRAIGHT_JOIN or by changing the INNER JOIN to STRAIGHT_JOIN.

Upvotes: 1

Related Questions