gistfile1.txt

mysql> explain SELECT   * FROM   (SELECT     pic.nid,     (       SELECT         pic2.sid       FROM         project_issue_comments pic2       WHERE         pic2.nid = pic.nid AND         pic2.comment_number = pic.comment_number - 1     ) as "original_state",     (       SELECT         pis2.name       FROM         project_issue_state pis2       WHERE         pis2.sid = original_state     ) as "original_state_name",     pic.sid as "new_state",     pis.name as "new_state_name",     pic.comment_number,     pic.timestamp   FROM     project_issue_comments pic   JOIN     project_issue_state pis ON pic.sid = pis.sid   WHERE     pic.nid = 8) as issuedata WHERE   issuedata.original_state <> issuedata.new_state;
+----+--------------------+------------+--------+------------------------------+---------------+---------+-----------------------+------+-------------+
| id | select_type        | table      | type   | possible_keys                | key           | key_len | ref                   | rows | Extra       |
+----+--------------------+------------+--------+------------------------------+---------------+---------+-----------------------+------+-------------+
|  1 | PRIMARY            | <derived2> | ALL    | NULL                         | NULL          | NULL    | NULL                  |  185 | Using where | 
|  2 | DERIVED            | pic        | ref    | nid_timestamp                | nid_timestamp | 5       |                       |  182 | Using where | 
|  2 | DERIVED            | pis        | eq_ref | PRIMARY                      | PRIMARY       | 4       | issues_drupal.pic.sid |    1 | Using where | 
|  4 | DEPENDENT SUBQUERY | pis2       | eq_ref | PRIMARY                      | PRIMARY       | 4       | func                  |    1 | Using where | 
|  3 | DEPENDENT SUBQUERY | pic2       | ref    | nid_timestamp,comment_number | nid_timestamp | 5       | issues_drupal.pic.nid |    6 | Using where | 
+----+--------------------+------------+--------+------------------------------+---------------+---------+-----------------------+------+-------------+
5 rows in set (0.32 sec)