optimization - Mysql - Help for optimizing query -


i have query:

select bi.id,         bi.location,         bi.expense_group,         bi.level,         bi.is_active,         bi.type,         full_name,         ( bl.bud_amount )                                     budgetamount,         ( coalesce(( ( bl.bud_amount * 3 ) - (                      + bal.bal_amount1 + bal.bal_amount2                      + bal.bal_amount3 ) ), 0) ) difference,         ( coalesce(round(( + bal.bal_amount1 + bal.bal_amount2                            + bal.bal_amount3 ) / 3), 0) )     average,         bal.bal_amount1                                       bal1,         bal.bal_amount2                                       bal2,         bal.bal_amount3                                       bal3    (select *            budget_items bi           bi.location not null) bi         left join (select budget_item_id,                           sum(case                                 when budget_id = 21491 amount                               end) bud_amount                      budget_lines                    group  budget_item_id) bl                on bl.budget_item_id = bi.id         join (select budget_item_id,                      ifnull(sum(case                                   when balance_id = 12841 amount                                 end), 0) bal_amount1,                      ifnull(sum(case                                   when balance_id = 18647 amount                                 end), 0) bal_amount2,                      ifnull(sum(case                                   when balance_id = 18674 amount                                 end), 0) bal_amount3                 balance_lines               group  budget_item_id) bal           on bal.budget_item_id = bi.id  order  bi.location

it takes lot of time. in budget_lines , balance_lines tables have more 5,000,000 rows in each.

i attach explain of query, you'll ne able see problem. ids in every table indexed. there column if indexed spped query? or maybe need change it.

*** left join necessary because need items nudget_items, if don't exist in balance/budget_line table.

schema is: every budget has budget_lines. every balance has balance_lines. query aimed have 1 table summarize differences between budget , several balances.

enter image description here

you can see bigger image here: http://i.stack.imgur.com/dlf8v.png

edit: after @sebas answers: enter image description here

for @sabes hunger, put here describe: budget_items enter image description here

budget_lines enter image description here

balance_lines enter image description here

maybe this; without sample data, , indexes it's difficult see

select *    budget_items bi   bi.location not null) bi  inner join  --added inner clarity  -changed order inner's before outers.   (select budget_item_id, sum(case when balance_id = 12841 coalesce(amount,0) end), 0) bal_amount1,                            sum(case when balance_id = 18647 coalesce(amount,0) end), 0) bal_amount2,                            sum(case when balance_id = 18674 coalesce(amount,0) end), 0) bal_amount3       balance_lines     balance_id in (12841, 18647, 18674) --this way balance_ids aren't in list don't evaluated    group  budget_item_id) bal   on bal.budget_item_id = bi.id  left join   (select budget_item_id, sum(case when budget_id = 21491 coalesce(amount,0) end) bud_amount      budget_lines    budget_id = 21491 --again since care budget_id 21491, can limit results    group  budget_item_id) bl   on bl.budget_item_id = bi.id

Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } &l
Read more

ruby on rails - Seeing duplicate requests handled with Unicorn -

recently upgraded our ec2 instances c3.large in order boost more unicorn workers handle increased traffic our site. (6xworkers per machine - 2 ec2 instances). when did this, started seeing duplicate records being created! looks somehow, maybe 2 unicorn workers attempting process same request? in nginx logs see 1 [post] request particular rails controller - yet 2 records being created in database. i'm hitting kind of race condition issue - have no idea how debug this. doesn't happen time, when it's frustrating well. noticed 2 database records within 5 seconds of each other. (and yes disable button when user clicks submit button). unicorn 4.8.2 ruby 1.9.3 rails 3.2.14 any great! thanks! although may not fix problem, upgrade ruby 2.0.0. there blog mentioning idea upgrade ruby 2.0.0 if using unicorn. for example: https://www.digitalocean.com/community/articles/how-to-optimize-unicorn-workers-in-a-ruby-on-rails-app specifically: if using ruby 1.9,
Read more