How to avoid spending time in creating tmp table in MySQL

Question

I have the following MYSQL query.

SELECT 
    COUNT(analyzer_host.server) AS count,
    analyzer_host.server AS server
FROM
    analyzer_host,
    analyzer_url,
    analyzer_code
WHERE
    analyzer_host.server IS NOT NULL
        AND analyzer_host.server != ''
        AND analyzer_code.account_id = 33
        AND analyzer_code.id = analyzer_url.url_id
        AND analyzer_url.id = analyzer_host.url_id
GROUP BY analyzer_host.server;

I did some profiling on this query and this is stuck in "Copying to tmp table" . Is there a way I can avoid that. Also any pointers in what is causing the query to create tmp tables.

Answer 1

First

SELECT  COUNT(host.server) AS count, host.server AS server
    FROM  host
    JOIN  url  ON url.id  = host.url_id
    JOIN  code ON code.id =  url.url_id
    WHERE  host.server IS NOT NULL
      AND  host.server != ''
      AND  code.account_id = 33
    GROUP BY  host.server; 

That gets rid of analyzer_ clutter and use JOIN...ON syntax.

Second, it seems that the JOINs are not quite right -- is there both an id and a url_id in url? Is the url_id different between host and url?

Does code have PRIMARY KEY(account_id)? That is where the optimizer would like to start.

Please provide EXPLAIN SELECT ... so we can see if it is doing any table scans. If it is, then that is the problem, not the "tmp table".

Please provide SHOW CREATE TABLE for all three tables if you need further discussion.