All geek questions in one place

Left join optimization

I have a database similar to the simplified one below. I need to get the columns: col8, col9, col10, col11, col12 (the ones I circled

Currently, I, m, using the left join to join each table, but this leads to a very long query (there are a lot of records). When profiling, writing to the tmp table has the greatest impact.

I limit the result to 24, but due to the left join, it still copies thousands of records to the tmp table.

http://oberto.co.nz/demo/assets/db2.jpeg

Can this be optimized to still fetch the surrounded column from each table using join with pk?

Thanks.

+4
source share
2 answers
SELECT a.col12, b1.col8, c1.col9, d1.col10, e1.col11 FROM a INNER JOIN (SELECT b.col8, b.col2, b.col3 FROM b WHERE b.col2 = a.col2 GROUP BY b.col8) b1 ON (b1.col2 = a.col2) INNER JOIN (SELECT c.col9, c.col3, c.col4 FROM c WHERE c.col3 = b1.col3 GROUP BY c.col9) c1 ON (c1.col3 = b1.col3) INNER JOIN (SELECT d.col10, d.col4 FROM d WHERE d.col4 = c1.col4 GROUP BY d.col10) d1 ON (d1.col4 = c1.col4) INNER JOIN (SELECT e.col11, e.col6 FROM e WHERE e.col6 = a.col6 GROUP BY e.col11) e1 ON (e1.col6 = a.col6)

You will no longer have duplicate rows.
You may need to experiment with LEFT instead of INNER . And if you do not need a subtitle, you must eliminate it, because it slows down.

+1
source

in its current and simplest form, I would have a query like ...

 SELECT STRAIGHT_JOIN a.col1, a.col12, b.col8, c.col9, d.col10, e.col11 FROM a left join b on a.col2 = b.col2 left join c on b.col3 = c.col3 left join d on c.col4 = d.col4 left join e on a.col6 = e.col6

Nevertheless, it will be adjusted as soon as I return to other criteria, filters, conditions, requirements for the left / inner connection, which can help in the future to optimize.

0
source

Source: https://habr.com/ru/post/1381639/

More articles:


All Articles