mysql using temporary table with subqueries, but not group by and order by

Question

I have the following mysql query which is taking about 3 minutes to run. It does have 2 sub queries, but the tables have very few rows. When doing an explain, it looks like the "using temporary" might be the culprit. Apparently, it looks like the database is creating a temporary table for all three queries as noted in the "using temporary" designation below.

What confused me is that the MySQL documentation says, that using temporary is generally caused by group by and order by, neither of which I'm using. Do the subqueries cause an implicit group by or order by? Are the sub-queries causing a temporary table to be necessary regardless of group by or order by? Any recommendations of how to restructure this query so MySQL can handle it more efficiently? Any other tuning ideas in the MySQL settings?

mysql> explain
SELECT DISTINCT COMPANY_ID, COMPANY_NAME
  FROM COMPANY
 WHERE ID IN (SELECT DISTINCT ID FROM CAMPAIGN WHERE CAMPAIGN_ID IN (SELECT
              DISTINCT CAMPAIGN_ID FROM AD
              WHERE ID=10 AND (AD_STATUS='R' OR AD_STATUS='T'))
              AND (STATUS_CODE='L' OR STATUS_CODE='A' OR STATUS_CODE='C'));

+----+--------------------+----------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type        | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+--------------------+----------+------+---------------+------+---------+------+------+------------------------------+
|  1 | PRIMARY            | COMPANY  | ALL  | NULL          | NULL |    NULL | NULL | 1207 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | CAMPAIGN | ALL  | NULL          | NULL |    NULL | NULL |  880 | Using where; Using temporary |
|  3 | DEPENDENT SUBQUERY | AD       | ALL  | NULL          | NULL |    NULL | NULL |  264 | Using where; Using temporary |
+----+--------------------+----------+------+---------------+------+---------+------+------+------------------------------+

thanks! Phil

Answer 1

I don't know the structure of your schema, but I would try the following:

CREATE INDEX i_company_id ON company(id);   -- should it be a Primary Key?..
CREATE INDEX i_campaign_id ON campaign(id); -- same, PK here?
CREATE INDEX i_ad_id ON ad(id);             -- the same question applies
ANALYZE TABLE company, campaign, ad;

And your query can be simplified like this:

SELECT DISTINCT c.company_id, c.company_name
  FROM company c
  JOIN campaign cg ON c.id = cg.id
  JOIN ad ON cg.campaign_id = ad.campaign_id
 WHERE ad.id = 10
   AND ad.ad_status IN ('R', 'T')
   AND ad.status_code IN ('L', 'A', 'C');

DISTINCT clauses in the subqueries are slowing down things significantly for you, the final one is sufficient.