Optimize SQL-Query several JOINs

Question

I have an SQL query with a nested join:

SELECT rh.host, rh.report, COUNT(results.id), COUNT(results_2.id), COUNT(results_3.id), COUNT(results_4.id)
FROM report_hosts rh
INNER JOIN report_results rr ON rh.report = rr.report
LEFT OUTER JOIN results ON rr.result = results.id AND results.type =  'Hole' AND results.host = rh.host
LEFT OUTER JOIN results results_2 ON rr.result = results_2.id AND results_2.type =  'Warning' AND results_2.host = rh.host
LEFT OUTER JOIN results results_3 ON rr.result = results_3.id AND results_3.type =  'Note' AND results_3.host = rh.host
LEFT OUTER JOIN results results_4 ON rr.result = results_4.id AND results_4.type =  'Log' AND results_4.host = rh.host
GROUP BY rh.host

The query as-is takes about 5sec with 99.7% copying to temp table. An EXPLAIN of the full query shows as:

+----+-------------+-----------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
| id | select_type | table     | type   | possible_keys | key     | key_len | ref               | rows | Extra                           |
+----+-------------+-----------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
|  1 | SIMPLE      | rr        | ALL    | report        | NULL    | NULL    | NULL              | 3139 | Using temporary; Using filesort |
|  1 | SIMPLE      | rh        | ref    | report        | report  | 5       | openvas.rr.report |  167 | Using where                     |
|  1 | SIMPLE      | results   | eq_ref | PRIMARY,type  | PRIMARY | 4       | openvas.rr.result |    1 |                                 |
|  1 | SIMPLE      | results_2 | eq_ref | PRIMARY,type  | PRIMARY | 4       | openvas.rr.result |    1 |                                 |
|  1 | SIMPLE      | results_3 | eq_ref | PRIMARY,type  | PRIMARY | 4       | openvas.rr.result |    1 |                                 |
|  1 | SIMPLE      | results_4 | eq_ref | PRIMARY,type  | PRIMARY | 4       | openvas.rr.result |    1 |                                 |
+----+-------------+-----------+--------+---------------+---------+---------+-------------------+------+---------------------------------+

When I remove the LEFT JOINs, the query executes in about 1s, each LEFT JOIN adds about one additional second execution time.

My question: Can anyone explain, why the copy to temp table task of one join takes longer if there are more LEFT JOINs? Is MySQL copying the temp table several times for each JOIN?

How can I avoid this? Am I missing an index?

What I intend to accomplish: I have a table with scanning results of several hosts. Each result is classified in types ( "Hole", "Warning", "Note" or "Log"). I want to select each host and the corresponding amount of Holes, Warnings, Notes and Logs. As a "restriction" I have the fact, that not each host has each type of results.

If results1 and results2 both have 10 matching records, you now have 100 records. And each left join multiplies that further. This is not the query you are looking for. [I'd post more, but I'm on a phone] — MatBailie
– MatBailie, Commented Jul 2, 2012 at 9:07

Ilion · Accepted Answer · 2012-07-02 09:26:40Z

3

You're joining a single table several times, which effectively is like joining multiple tables. You should be able to handle that with some case statements and a where clause instead. (In fact you may not need the where clause.)

SELECT rh.host, rh.report, 
 COUNT(CASE WHEN results.type = 'Hole' THEN 1 ELSE NULL END) as Holes, 
 COUNT(CASE WHEN results.type = 'Warning' THEN 1 ELSE NULL END) as Warnings,
 COUNT(CASE WHEN results.type = 'Note' THEN 1 ELSE NULL END) as Notes, 
 COUNT(CASE WHEN results.type = 'Log' THEN 1 ELSE NULL END) as Logs
FROM 
 report_hosts rh
INNER JOIN 
 report_results rr 
ON 
 rh.report = rr.report
LEFT OUTER JOIN 
 results 
ON 
 rr.result = results.id 
 AND results.host = rh.host
WHERE
 results.type = 'Hole' 
 OR results.type = 'Warning' 
 OR results.type = 'Note' 
 OR results.type = 'Log'
GROUP BY rh.host, rh.report

Case statements, IME, are not the greatest performers, but your data bloat from the many joins may offset that and give this better performance.

edited Jul 2, 2012 at 9:26

answered Jul 2, 2012 at 9:17

Ilion

6,8923 gold badges28 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Manuel Faux Over a year ago

Great idea to avoid the multiple-joins! The query takes only 1sec. But, one weird thing: If I omit the WHERE clause, the execution takes more than 10sec. The results table has 6000 rows, but only 600 rows do not match the WHERE clause.

Mihai Stancu Over a year ago

If results.type is an index they it's counting they indexed values when using WHERE when not using WHERE it's doing a full table scan.

Manuel Faux Over a year ago

So the reason is, that only a WHERE clause can "request" an index, but not an aggregate function?

Mihai Stancu · Accepted Answer · 2012-07-02 09:09:29Z

1

Using a lot of data (in your case an extra left join) will mean storing it in memory.

If you deplete the buffers your query will need to be stored to a temporary result table on drive.

Try using the same number of left joins but limiting the number of rows with a limit. It should confirm that the problem lies in the buffers (meaning it will run faster).

answered Jul 2, 2012 at 9:09

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

6 Comments

Manuel Faux Over a year ago

I recognized, the problem are the LEFT JOINs. With INNER JOINs, it's much faster, but I guess my query is not doable with inner joins, is it?

YvesR Over a year ago

Inner join Mostly handle less data, that is why it is faster in direct comparison.

Mihai Stancu Over a year ago

doable depends on your needs. Left joins grab all the values in the left-side table and any available values for the right-side table (nulls otherwise). Inner joins only grab the values on the left-side that have correspondents on the right-side. Which as YvesR said means less values. I have no idea what your tables are used for or what the data in them is so i can't tell you if selecting more ore less data will be ok.

Manuel Faux Over a year ago

I have added my aim I want to accomplish with the query. Maybe SQL is not the best way to accomplish this and I should do the "heavy" part in my programming language.

Mihai Stancu Over a year ago

If the query @Ilion proposed works and yield the results you'd expect I'd recommend it.

|

Collectives™ on Stack Overflow

Optimize SQL-Query several JOINs

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related