Databases
StackOverflow2013
Postsdbo
POMySQL LEFT JOIN query is very slow
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL LEFT JOIN query is very slow
    primarykey
    data
    text
    <pre><code>SELECT bi.id, bi.location, bi.expense_group, bi.level, bi.is_active, bi.type, full_name, Sum(DISTINCT bl.amount) AS BudgetAmount, Sum(DISTINCT Ifnull(( bl.amount * 6 ) - ( + bal1.amount + bal2.amount + bal3.amount + bal4.amount + bal5.amount + bal6.amount ), 0)) AS Difference, Sum(DISTINCT Ifnull(Round(( + bal1.amount + bal2.amount + bal3.amount + bal4.amount + bal5.amount + bal6.amount ) / 6), 0) ) AS Average, Sum(DISTINCT bal1.amount) AS BAL1, Sum(DISTINCT bal2.amount) AS BAL2, Sum(DISTINCT bal3.amount) AS BAL3, Sum(DISTINCT bal4.amount) AS BAL4, Sum(DISTINCT bal5.amount) AS BAL5, Sum(DISTINCT bal6.amount) AS BAL6 FROM (SELECT * FROM budget_items WHERE bi.location IS NOT NULL) AS bi LEFT JOIN budget_lines AS bl ON bi.id = bl.budget_item_id AND bl.budget_id = 5983 LEFT JOIN balance_lines AS bal1 ON bi.id = bal1.budget_item_id AND bal1.balance_id = 28839 LEFT JOIN balance_lines AS bal2 ON bi.id = bal2.budget_item_id AND bal2.balance_id = 28633 LEFT JOIN balance_lines AS bal3 ON bi.id = bal3.budget_item_id AND bal3.balance_id = 26664 LEFT JOIN balance_lines AS bal4 ON bi.id = bal4.budget_item_id AND bal4.balance_id = 14500 LEFT JOIN balance_lines AS bal5 ON bi.id = bal5.budget_item_id AND bal5.balance_id = 10199 LEFT JOIN balance_lines AS bal6 ON bi.id = bal6.budget_item_id AND bal6.balance_id = 7204 GROUP BY bi.id ORDER BY bi.position </code></pre> <p>As you can see actually there are only 3 tables, but I need to query one of them for each of the balances. This is taking a lot of time. Where is my mistake?</p> <hr> <p>After struggling with @Gordon Linoff post, I tried to fix it myself. @Gordon Linoff is that what you meant?</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 * 6 ) - (bal.bal_amount1 + bal.bal_amount2 + bal.bal_amount3 + bal.bal_amount4 + bal.bal_amount5 + bal.bal_amount6), 0)) AS Difference, (coalesce(Round(( bal.bal_amount1 + bal.bal_amount2 + bal.bal_amount3 + bal.bal_amount4 + bal.bal_amount5 + bal.bal_amount6 ) / 6), 0) ) AS Average, bal.bal_amount1 AS BAL1, bal.bal_amount2 AS BAL2, bal.bal_amount3 AS BAL3, bal.bal_amount4 AS BAL4, bal.bal_amount5 AS BAL5, bal.bal_amount6 AS BAL6 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 = 5983 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 = 28839 then amount end), 0) AS bal_amount1, IFNULL(Sum(case when balance_id = 28633 then amount end), 0) AS bal_amount2, IFNULL(Sum(case when balance_id = 26664 then amount end), 0) AS bal_amount3, IFNULL(Sum(case when balance_id = 14500 then amount end), 0) AS bal_amount4, IFNULL(Sum(case when balance_id = 10199 then amount end), 0) AS bal_amount5, IFNULL(Sum(case when balance_id = 7204 then amount end), 0) AS bal_amount6 from balance_lines group by budget_item_id ) AS bal on bal.budget_item_id = bi.id ORDER BY bi.location </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNoam B.
    1. USNoam B.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL LEFT JOIN query is very slow
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL LEFT JOIN query is very slow
      1. USNoam B.
      1. VTFavorite
    1. COcheck your indexes
      singulars
      1. POMySQL LEFT JOIN query is very slow
      1. USic3b3rg
    2. COWhat platform are you using (MySQL, MSSQL, Oracle)?
      singulars
      1. POMySQL LEFT JOIN query is very slow
      1. USKermit
    3. COFrom the IfNull, it looks to me like it's MySQL
      singulars
      1. POMySQL LEFT JOIN query is very slow
      1. USDWright
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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