Re: Very slow query

[Aram Mirzadeh]

I couldn't get the provided sql but it wasn't due to chassis, it was the email address. It couldn't figure out how to pull that of all things. I tried moving the JOIN to the derived table but that didn't help. Instead of futzing with it, I removed the email column since it shouldn't make a difference and the resulting query ran for longer than the original. I cancelled it after 30 seconds.

Here is chassis -- I'm removing a bunch of columns since they're non-consequential.

CREATE TABLE `chassis` (
  `assessmentId` varchar(20) collate utf8_unicode_ci NOT NULL,
  `deviceID` int(11) unsigned NOT NULL auto_increment,
...
  UNIQUE KEY `deviceID` (`deviceID`),
  KEY `assessmentId` (`assessmentId`)
) ENGINE=InnoDB AUTO_INCREMENT=607100 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

... and the explain

 
+----+--------------------+------------+--------+---------------+--------------+---------+-------------+------+---------------------------------+
| id | select_type        | table      | type   | possible_keys | key          | key_len | ref         | rows | Extra                           |
+----+--------------------+------------+--------+---------------+--------------+---------+-------------+------+---------------------------------+
|  1 | PRIMARY            | <derived2> | ALL    | NULL          | NULL         | NULL    | NULL        |  114 | Using temporary; Using filesort | 
|  1 | PRIMARY            | c          | ref    | assessmentId  | assessmentId | 62      | x.id        |  719 | Using index                     | 
|  1 | PRIMARY            | status     | eq_ref | PRIMARY,id    | PRIMARY      | 4       | x.status_fk |    1 |                                 | 
|  2 | DERIVED            | a          | ALL    | status        | NULL         | NULL    | NULL        |  481 | Using where                     | 
|  3 | DEPENDENT SUBQUERY | c          | ref    | assessmentId  | assessmentId | 62      | TLMA25.a.id |  719 | Using where; Using index        | 
+----+--------------------+------------+--------+---------------+--------------+---------+-------------+------+---------------------------------+