slow count query - explain shows temp and filesort

Question

have following query which runs slow, It produces a list of ACTION records with "action.typeid=1" and also counts if an ACTION record with "typeid=2" exists. It is this count which is slowing things - it uses temporary and filesort!!! can you help me find out how to improve.

EXPLAIN 
SELECT 
  action.actionid 
FROM
  ACTION 
  INNER JOIN EVENT 
    ON action.eventid = event.eventid 
  LEFT JOIN 
    (SELECT 
      COUNT(1),
      action.eventid 
    FROM
      ACTION 
    WHERE (action.typeid = '2') 
    GROUP BY action.eventid) AS act 
    ON act.eventid = event.eventid 
WHERE actiondate2 BETWEEN 20130601 
AND 20131031
AND event.siteid = 1
AND action.typeid = 1

The following indexes exist

CREATE INDEX idx_cusid ON `event` (cusid);
CREATE INDEX idx_actiontypeid ON `action` (typeid);
CREATE INDEX idx_actioneventid ON `action` (eventid);
CREATE INDEX idx_actiondate ON `action` (actiondate2);
CREATE INDEX idx_eventsiteid ON `event` (siteid);

Answer 1

Sir, the requirements in the question are still unclear to me.

I am going to explain my confusion using examples.

Please take a look at a simple demo: http://sqlfiddle.com/#!2/19f52c/6

This demo contains a simplified (for a sake of clarity) database structure and queries.

The query from the question (the first query in the demo) returns the following results:

SELECT action.actionid 
FROM ACTION 
  INNER JOIN EVENT 
    ON action.eventid = event.eventid 
  LEFT JOIN 
    (SELECT 
      COUNT(1),
      action.eventid 
    FROM
      ACTION 
    WHERE (action.typeid = '2') 
    GROUP BY action.eventid) AS act 
    ON act.eventid = event.eventid 
WHERE -- actiondate2 BETWEEN 20130601 AND 20131031 AND
 event.siteid = 1
AND action.typeid = 1
;
+ ------------- +
| actionid      |
+ ------------- +
| 1             |
| 3             |
| 5             |
+ ------------- +

Hovever, in the above query the subquery with alias ACT is simply ... useless.

The query executes this subquery (consuming time and server resources), then ... ignores its results, just throws them away.

The above query is equivalent to the below query (the second query in the demo) - it returns identical results as the query from the question, but without using the subquery (saving time and resources, therefore will perform much better):

SELECT action.actionid 
FROM
  ACTION 
  INNER JOIN EVENT 
    ON action.eventid = event.eventid 
WHERE -- actiondate2 BETWEEN 20130601 AND 20131031 AND
 event.siteid = 1
AND action.typeid = 1
;
+ ------------- +
| actionid      |
+ ------------- +
| 1             |
| 3             |
| 5             |
+ ------------- +

If your intent is to optimize the query show in your question - then please simply use the query shown above, this is the answer to your question.

However, looking at your comments about expected results, it appears that the query in the question is probably wrong - it doesn't give expected results.

Well, but it's still unclear what the query should give ? There are many possibilities, I'll show some of them below.

If you need to list all action.actionid with typeid = 1, but only such records, for which it exists any record with the same eventid and typeid = 2 ... then use the below query (the 3rd query in the demo):

SELECT a.actionid 
FROM
  ACTION a
  INNER JOIN EVENT e
    ON a.eventid = e.eventid 
WHERE -- actiondate2 BETWEEN 20130601 AND 20131031 AND
  EXISTS ( SELECT 1 FROM action a1
              WHERE a1.eventid = a.eventid
              AND a1.typeid = 2
         ) 
   AND e.siteid = 1
   AND a.typeid = 1
;

+ ------------- +
| actionid      |
+ ------------- +
| 1             |
| 3             |
+ ------------- +

This query uses the EXISTS operator, instead of COUNT() - if we want an information that same record exist, we don't need to count all of them! The count must read all of records, the EXISTS stops reading the table if it finds the first record that meets conditions - therefore EXISTS is usually faster than COUNT().

If you need to list all action.actionid with typeid = 1,and also display an information that some corresponding records exist with typeid = 2 - then use the below query (the fourth query in the demo):

SELECT 
  a.actionid ,
  CASE WHEN EXISTS ( SELECT 1 FROM action a1
              WHERE a1.eventid = a.eventid
              AND a1.typeid = 2
         ) 
       THEN 'typeid=2 EXISTS'
       ELSE 'typeid=2 DOESN''T EXIST'
  END typeid2_exist
FROM
  ACTION a
  INNER JOIN EVENT e
    ON a.eventid = e.eventid 
WHERE -- actiondate2 BETWEEN 20130601 AND 20131031 AND
   e.siteid = 1
   AND a.typeid = 1
;
+ ------------- + ---------------------- +
| actionid      | typeid2_exist          |
+ ------------- + ---------------------- +
| 1             | typeid=2 EXISTS        |
| 3             | typeid=2 EXISTS        |
| 5             | typeid=2 DOESN'T EXIST |
+ ------------- + ---------------------- +

But if you really need to count corresponding records with typeid = 2 - then this query can help (the fifth query in the demo):

SELECT 
  a.actionid ,
  ( SELECT count(*) FROM action a1
              WHERE a1.eventid = a.eventid
              AND a1.typeid = 2
   ) typeid2_count
FROM
  ACTION a
  INNER JOIN EVENT e
    ON a.eventid = e.eventid 
WHERE -- actiondate2 BETWEEN 20130601 AND 20131031 AND
   e.siteid = 1
   AND a.typeid = 1
;
+ ------------- + ------------------ +
| actionid      | typeid2_count      |
+ ------------- + ------------------ +
| 1             | 1                  |
| 3             | 1                  |
| 5             | 0                  |
+ ------------- + ------------------ +

If none of queries shown above meet your requirements, please show (basing on sample data in the demo) the results that the query should return - this helps someone in this forum to build a proper query that meets all your requirements.

Then, when we recognize the right query that meet all expectations, we can start to optimize it's performance.