mysql - Slow performance on joining table -


i trying optimize query below , have study how add index improve peformance , result still slow. query below took 20 sec run , transaction contains around 100k records , joining table transactiondetail contains around 500k records. 

    select transaction.id ....     transaction     inner join agent on agent.id = transaction.agent_id     inner join distributor on distributor.id = transaction.distributor_id     inner join transactiondetail on transaction.id = transactiondetail.transaction_id     transactiondetail.type =  'admin'     , transaction.status in ('pending',  'processing',  'success',  'rejected')     order issued_date desc limit 0 , 10

from query above try apply understand indexing

  1. i added 3 index because of inner join , transaction(agent_id,distributor_id) , transaction(transaction_id)

  2. from clause added transaction(status)

  3. because of order added transaction(issued_date)

but doesn't show improvement below explain

enter image description here

and screenshot phpmyadmin show indexing table transaction

enter image description here

is there way improvement query? or optimised , should focus on mysql configuration?

your query

select transaction.id .... transaction inner join agent on agent.id = transaction.agent_id inner join distributor on distributor.id = transaction.distributor_id inner join transactiondetail on transaction.id = transactiondetail.transaction_id transactiondetail.type =  'admin' , transaction.status in ('pending',  'processing',  'success',  'rejected') order issued_date desc limit 0 , 10

now have indexes applied on tables good, in clause more or , creates real performance issue. in case of small data-set not observed in large data-set performance dropped significantly.

one method of optimizing convert in clause union works better or in

(  select transaction.id ....  transaction  inner join agent on agent.id = transaction.agent_id  inner join distributor on distributor.id = transaction.distributor_id  inner join transactiondetail on transaction.id = transactiondetail.transaction_id  transactiondetail.type =  'admin'  , transaction.status = 'pending' ) union (  select transaction.id ....  transaction  inner join agent on agent.id = transaction.agent_id  inner join distributor on distributor.id = transaction.distributor_id  inner join transactiondetail on transaction.id = transactiondetail.transaction_id  transactiondetail.type =  'admin'  , transaction.status = 'processing' ) union (  select transaction.id ....  transaction  inner join agent on agent.id = transaction.agent_id  inner join distributor on distributor.id = transaction.distributor_id  inner join transactiondetail on transaction.id = transactiondetail.transaction_id  transactiondetail.type =  'admin'  , transaction.status = 'success' ) union (  select transaction.id ....  transaction  inner join agent on agent.id = transaction.agent_id  inner join distributor on distributor.id = transaction.distributor_id  inner join transactiondetail on transaction.id = transactiondetail.transaction_id  transactiondetail.type =  'admin'  , transaction.status = 'rejected' ) order issued_date desc limit 0 , 10

for resolving order may need add index as

alter table transaction add index status_created_idx(status,issued_date);

Comments

Post a Comment

Popular posts from this blog

angularjs - ADAL JS Angular- WebAPI add a new role claim to the token -

i have created angular spa webapi backend using adal js authentication.as there no roles in ad, need manually add role claims in order give users access different api controllers. the roles stored in database. expecting inject claim through call webapi after authentication ad. webapi code might this. identity.addclaim(new claim("role", "user")); var ticket = new authenticationticket(identity, props); var accesstoken = startup.oauthbeareroptions.accesstokenformat.protect(ticket); is possible replace adal idtoken new token? is viable solution or there other better way handle this? as initial token generated azuread, possible edit token add new claim? appreciated. graph api supporting group claims. see here: http://justazure.com/azure-active-directory-part-4-group-claims/ if @ examples on page, users assigned groups , app can check group in claims. in current version of portal, need app manifest , modify it.
Read more

node.js - Using Node without global install -

i'm working on few node & electron (atom shell) projects , i'm curious how them work without user having install node globally. i've seen few applications including node directory in project contains node.exe binary. how go setting node project use binary? edit: thought should add, i've searched everywhere answer this, unrelated results.
Read more

php - CakePHP HttpSockets send array of paramms -

i problem when make request (i can request). $link = 'http://uri/path/?param1=1&sid=2&sid=3&sid=4' $http = $httpsocket = new httpsocket(); $res = $httpsocket->get($link); if check request pr($http); i see : [line] => /url/path/?param1=1&sid%5b0%5d=2&sid%5b1%5d=3&sid%5b2%5d=4 http/1.1 and i'm getting empty response because server doesn't know parameters sid%5b0%5d=2 when try send parameters array : $data = array('param1' => '1', 'sid' => array('2','3', '4')); i see same changes %5b0%5d - additional indexes. how fix it? unfortunately , can not change server side, if send request in browser, i'll normal response in json format. when make request, can parameters this: $param1 = $_get['param1']; $sid= $_get['sid']; tip: use diferente names parameters. have multiples sid. use sid1, sid2, sid3... update: can parameters this:...
Read more