mysql - Left join - how to be more efficient? -


i wrote query difficult read , im afraid efficiency might bad. can query written more efficiently in terms of matching consumerid's each table consumerdata.consumerid?

select   consumerdata.consumerid,  signupdate,  city,  state,  year(dob),  topaffiliate,  activestatus,  lastuseddate,  (select sum(achload.transactionamount) achload achload.consumerid = consumerdata.consumerid) total_ach, (select sum(billpay.transactionamount) billpay billpay.consumerid = consumerdata.consumerid) bill_pay, (select sum(recharge.transactionamount) recharge recharge.consumerid = consumerdata.consumerid) revenue, (select count(cash.consumerid) cash cash.consumerid = consumerdata.consumerid) cash__txns, (select sum(moneytransfer.transactionamount) moneytransfer moneytransfer.consumerid = consumerdata.consumerid) transfer, (select sum(moneytransfer.commissionfeeamount) moneytransfer moneytransfer.consumerid = tbl_accounts_consumerdata.consumerid) commission_fee, (select count(interchangetransactions.transactiondescription) interchangetransactions interchangetransactions.transactiondescription = "withdrawal" , interchangetransactions.consumerid = consumerdata.consumerid) withdrawals, (select count(interchangetransactions.responsecode) interchangetransactions interchangetransactions.responsecode in (01) , interchangetransactions.consumerid = consumerdata.consumerid , interchangetransactions.transactiondescription = "withdrawal") code_01, (select count(interchangetransactions.responsecode) interchangetransactions interchangetransactions.responsecode in (02) , interchangetransactions.consumerid = consumerdata.consumerid , interchangetransactions.transactiondescription = "withdrawal" ) code_02, (select count(interchangetransactions.transactiondescription) interchangetransactions interchangetransactions.transactiondescription in ("2 cash advance", "1 cash advance") , interchangetransactions.consumerid = consumerdata.consumerid) cash_advance_count, (select count(interchangetransactions.transactiondescription) interchangetransactions interchangetransactions.transactiondescription in ("balance") , interchangetransactions.consumerid = consumerdata.consumerid) inquiry_count, (select sum(interchangetransactions.transactionamount) interchangetransactions interchangetransactions.transactiontypecode = 195 , interchangetransactions.consumerid = consumerdata.consumerid)  intnl_txn_sum, (select count(interchangetransactions.transactionamount) interchangetransactions interchangetransactions.transactiontypecode = 195 , interchangetransactions.consumerid = consumerdata.consumerid)  intnl_txn_count   consumerdata  left join achload on achload.consumerid=consumerdata.consumerid  left join  billpay on billpay.consumerid = consumerdata.consumerid  left join recharge on recharge.consumerid = consumerdata.consumerid  left join cash on cash.consumerid = consumerdata.consumerid  left join moneytransfer on moneytransfer.consumerid = consumerdata.consumerid  left join interchangetransactions on interchangetransactions.consumerid = consumerdata.consumerid  consumerdata.signupdate between date(20120101) , curdate() group consumerdata.consumerid order signupdate asc;

since you're not selecting of transaction tables, simple improvement rid of left join clauses. they're not being used anything, since you're getting totals correlated subqueries. causing database create huge cross product between tables, discard when group by @ bottom.

another way join subqueries calculate grouped totals of each transaction table, instead of correlated subqueries.

select      c.consumerid, signupdate, state, year(dob), topaffiliate, activestatus, lastuseddate,     ifnull(total_ach, 0) total_ach, ifnull(bill_pay, 0) bill_pay, ifnull(revenue, 0) revenue,      ifnull(cash__txns, 0) cash__txns, ... consumerdata c left join (     select consumerid, sum(transactionamount) total_ach     achload     group consumerid) on a.consumerid = c.consumerid left join (     select consumerid, sum(transactionamount) bill_pay     billpay     group consumerid) b on b.consumerid = c.consumerid left join (     select consumerid, sum(transactionamount) revenue     recharge     group consumerid) r on r.consumerid = c.consumerid left join (     select consumerid, count(*) cash__txns     cash     group consumerid) cash on cash.consumerid = c.consumerid ... c.signupdate between date(20120101) , curdate() order signupdate asc

i'm not sure way faster -- depend on how data there in transaction tables consumers signup dates outside range specify. try both , see better.


-

Comments

Post a Comment

Popular posts from this blog

angular - Is it possible to get native element for formControl? -

i've got angular2 reactive form . created formcontrol s , assigned input fields by [formcontrol]=... . understand creates nativeelement <-> formcontrol link. my question: possible nativeelement formcontrol ? wanna myformcontrol.nativeelement.focus() i can share 1 terrible solution works me. in reactive forms can use either 1) formcontroldirective ts mycontrol = new formcontrol('') template <input type="text" [formcontrol]="mycontrol"> or 2) formcontrolname ts myform: formgroup; constructor(private fb: formbuilder) {} ngoninit() { this.myform = this.fb.group({ foo: '' }); } template <form [formgroup]="myform"> <input type="text" formcontrolname="foo"> </form> so these directives write patch like 1) formcontroldirective const originformcontrolngonchanges = formcontroldirective.prototype.ngonchanges; formcontroldirective.proto
Read more

unity3d - Rotate an object to face an opposite direction -

i have object , b. when click on object b, rotates object b (a faces b). b doesn't face a. need following : when faces b, need face opposite direction. have code rotating @ b. how rotate face opposite direction after that? vector3 targetdirection = target - transform.position; float step = speed * time.deltatime; vector3 newdirection = vector3.rotatetowards (turretdome.transform.forward, targetdirection, step, 0.0f); turretdome.transform.rotation = quaternion.lookrotation (newdirection); you object facing object b, want inverse direction of object after that? objecta.transform.rotation = quaternion.inverse(objecta.transform.rotation) but lets assume example turretdome object a, (negate direction): turretdome.transform.rotation = quaternion.lookrotation (-newdirection); naturally, both of these snippets not show how smoothen rotation, seem know how use time.deltatime. just incase unsure, quaternion.lerp this
Read more

javascript - Why jQuery Select box change event is now working? -

i working on product filters user select select box according his/her preference not getting selected value user in jquery. html code:- <select name="product_filter" id="product_filter"> <option value="price_low_first">price : low high</option> <option value="price_high_first">price : high low</option> <option value="latest">latest</option> <option value="popular">most popular</option> </select> jquery code: $(function () { $("#product_filter").change(function () { alert("hi") var selectedtext = $(this).find("option:selected").text(); var selectedvalue = $(this).val(); alert("selected text: " + selectedtext + " value: " + selectedvalue); }); }); hi believe code perfect. once please check jquery.min.js
Read more