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.

Why are you grouping by and counting the same field? If you want all records for each server just use COUNT(*) — AdamMc331, Jun 25 '15 at 16:59
Group by is needed as later I am calculating the percentage of each server in the application with that information. — station, Jun 25 '15 at 17:27
Is an index created on `analyzer_host.server` column ? This index is crucial for MySql to perform `group by` optimization, read this link: https://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html — krokodilko, Jun 25 '15 at 17:35
Yes , but it is a part of a composite index with other columns as well. — station, Jun 25 '15 at 17:37
Please stop using implicit syntax. THis is a SQL antipattern and it is something that you shoudl have stopped using more than 20 years ago. — HLGEM, Jun 25 '15 at 18:09

score 0 · Answer 1 · answered Jun 26 '15 at 21:32

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.

How to avoid spending time in creating tmp table in MySQL

1 Answers1