StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql - Help for optimizing query
text
Body
copied!<p>I have this query:</p> <pre><code>SELECT bi.id, bi.location, bi.expense_group, bi.level, bi.is_active, bi.type, full_name, ( bl.bud_amount ) AS BudgetAmount, ( COALESCE(( ( bl.bud_amount * 3 ) - ( + bal.bal_amount1 + bal.bal_amount2 + bal.bal_amount3 ) ), 0) ) AS Difference, ( COALESCE(Round(( + bal.bal_amount1 + bal.bal_amount2 + bal.bal_amount3 ) / 3), 0) ) AS Average, bal.bal_amount1 AS BAL1, bal.bal_amount2 AS BAL2, bal.bal_amount3 AS BAL3 FROM (SELECT * FROM budget_items bi WHERE bi.location IS NOT NULL) AS bi LEFT JOIN (SELECT budget_item_id, Sum(CASE WHEN budget_id = 21491 THEN amount END) AS bud_amount FROM budget_lines GROUP BY budget_item_id) AS bl ON bl.budget_item_id = bi.id JOIN (SELECT budget_item_id, Ifnull(Sum(CASE WHEN balance_id = 12841 THEN amount END), 0) AS bal_amount1, Ifnull(Sum(CASE WHEN balance_id = 18647 THEN amount END), 0) AS bal_amount2, Ifnull(Sum(CASE WHEN balance_id = 18674 THEN amount END), 0) AS bal_amount3 FROM balance_lines GROUP BY budget_item_id) AS bal ON bal.budget_item_id = bi.id ORDER BY bi.location </code></pre> <p>It takes a lot of time. In the budget_lines and balance_lines tables I have more than 5,000,000 rows in each.</p> <p>I also attach the EXPLAIN of the query, so you'll ne able to see the problem. All ids in every table are indexed. Is there any column that if would be indexed spped up the query? Or maybe I need to change it.</p> <p>*** LEFT JOIN is necessary because I need to get all the items from nudget_items, even if they don't exist in the balance/budget_line table.</p> <p>Schema is: every budget has its budget_lines. Every balance has its balance_lines. The query is aimed to have ONE table to summarize the differences between a budget and several balances. </p> <p><img src="https://i.stack.imgur.com/dlF8V.png" alt="enter image description here"></p> <p>You can see a bigger image here: <a href="https://i.stack.imgur.com/dlF8V.png" rel="nofollow noreferrer">http://i.stack.imgur.com/dlF8V.png</a></p> <p><strong>EDIT:</strong> After @Sebas answers: <img src="https://i.stack.imgur.com/KLs4G.png" alt="enter image description here"></p> <p>For @sabes hunger, I put here the DESCRIBE: budget_items <img src="https://i.stack.imgur.com/DnJ78.png" alt="enter image description here"></p> <p>budget_lines <img src="https://i.stack.imgur.com/OYVEk.png" alt="enter image description here"></p> <p>balance_lines <img src="https://i.stack.imgur.com/QFgdZ.png" alt="enter image description here"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload