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:

output of explain:

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.

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