sql - mysql - query with multiple left joins takes too long -


i trying fetch data query has left joins on multiple tables. query returns 4132 rows , takes 4.55 sec duration, 31.30 sec fetch in mysql workbench. tried execute php takes same amount of time.

select                 aa.bams_id chassis_bams_id, aa.hostname chassis_hostname,      aa.rack_number chassis_rack, aa.serial_number chassis_serial, aa.site_id chassis_site_id,     cb.bay_number, cb.bsn serial_number_in_bay, case                 when a_a.bams_id null 'unknown'                 else a_a.bams_id end blade_bams_id,                 a_a.hostname blade_hostname, a_s.description blade_status, a_a.manufacturer blade_manufacturer, a_a.model blade_model,                 a_a.bookable_unit_id blade_bookable_unit_id, a_a.rack_number blade_rack_number, a_a.manufactured_date blade_manufactured_date,                 a_a.support_expired_date blade_support_expired_date, a_a.site_id blade_site_id all_assets aa                 left join manufacturer_model mm on aa.manufacturer = mm.manufacturer , aa.model = mm.model                 left join chassis_bays cb on aa.bams_id = cb.chassis_bams_id                 left join all_assets a_a on cb.bsn = a_a.serial_number                 left join asset_status a_s on a_a.status=a_s.status mm.hardware_type = 'chassis';

these definition of tables being used:

enter image description here

output of explain:

enter image description here

the query fetches data of each blade in each chassis. executed same query on other systems , takes 5 seconds fetch result.

how optimize query ?

update (resolved)

added indexes suggested experts here. below execution plan after adding indexes.

enter image description here

create indexes, non indexed reads slower index reads.

to determine causing slow performance, best tool use "query plan analyzer":

have here: mysql performance explain

try creating indexes on obvious reads take place.look @ fields play role when join , clause. if have indexes on fields performance should increase, if non index reads taking place.

if still problem best see how mysql fetching data, restructuring data or maybe altering way queuing can give better results.

eg. create index's for: aa.manufacturer_model, aa.manufacturer, aa.model , mm.hardware_type


Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more