PIFR retest SQL

retest.sql

SELECT
  pd.test_id AS test_id,
  -- Dataset is too large; sort in PHP instead.
  pd.last_tested
FROM pift_data pd
-- Using an external aggregate as filter/join condition for a query
-- is the exact point where MySQL melts down :-/
INNER JOIN (
  SELECT
    MAX(pd2.test_id) AS maxid
  FROM pift_data pd2
  GROUP BY nid
) maxids ON pd.test_id = maxids.maxid
INNER JOIN node n ON pd.nid = n.nid
INNER JOIN field_data_field_issue_status fdfis ON n.nid = fdfis.entity_id AND n.type = fdfis.bundle
INNER JOIN field_data_field_project fdfp ON n.nid = fdfp.entity_id AND n.type = fdfp.bundle
WHERE
  pd.type = 2
  AND pd.status = 4
  AND pd.last_tested < 1394402320
  AND fdfis.entity_type = 'node'
  -- Not sure whether you are in charge of these, but all of these should
  -- use = instead of IN
  AND fdfis.bundle IN ('project_issue')
  AND fdfis.field_issue_status_value IN ('14')
  AND fdfp.field_project_target_id IN (3060)
-- Why 3?
LIMIT 3 OFFSET 0;

+------+-------------+------------+--------+---------------------------------------------------------------+--------------------------+---------+-----------------------------------+--------+----------------------------------------------+
| id   | select_type | table      | type   | possible_keys                                                 | key                      | key_len | ref                               | rows   | Extra                                        |
+------+-------------+------------+--------+---------------------------------------------------------------+--------------------------+---------+-----------------------------------+--------+----------------------------------------------+
|    1 | PRIMARY     | fdfis      | ref    | PRIMARY,entity_type,bundle,entity_id,field_issue_status_value | field_issue_status_value | 5       | const                             |   4855 | Using where; Using temporary; Using filesort |
|    1 | PRIMARY     | n          | eq_ref | PRIMARY,node_type,nid                                         | PRIMARY                  | 4       | pift_drupal.fdfis.entity_id       |      1 | Using where                                  |
|    1 | PRIMARY     | fdfp       | ref    | bundle,entity_id,field_project_target_id                      | entity_id                | 4       | pift_drupal.fdfis.entity_id       |      1 | Using where                                  |
|    1 | PRIMARY     | pd         | ref    | PRIMARY,status,last_tested,type_id,type_nid                   | type_nid                 | 9       | const,pift_drupal.fdfis.entity_id |      4 | Using where                                  |
|    1 | PRIMARY     | <derived2> | ref    | key0                                                          | key0                     | 5       | pift_drupal.pd.test_id            |     10 |                                              |
|    2 | DERIVED     | pd2        | ALL    | NULL                                                          | NULL                     | NULL    | NULL                              | 148368 | Using temporary; Using filesort              |
+------+-------------+------------+--------+---------------------------------------------------------------+--------------------------+---------+-----------------------------------+--------+----------------------------------------------+
6 rows in set (0.00 sec)