Search code examples
mysqlsqlmysql-workbench

mysql - query with multiple left joins takes too long

I am trying to fetch data with a query which has left joins on multiple tables. The query returns 4132 rows and takes 4.55 sec duration, 31.30 sec fetch in mysql workbench. I even tried to execute it from php but that takes the same amount of time.

SELECT
                aa.bams_id AS chassis_bams_id, aa.hostname AS chassis_hostname, 
    aa.rack_number AS chassis_rack, aa.serial_number AS chassis_serial, aa.site_id AS chassis_site_id,
    cb.bay_number, cb.bsn AS serial_number_in_bay,
CASE
                WHEN a_a.bams_id IS NULL THEN 'Unknown'
                ELSE a_a.bams_id
END AS blade_bams_id,
                a_a.hostname AS blade_hostname, a_s.description AS blade_status, a_a.manufacturer AS blade_manufacturer, a_a.model AS blade_model,
                a_a.bookable_unit_id AS blade_bookable_unit_id, a_a.rack_number AS blade_rack_number, a_a.manufactured_date AS blade_manufactured_date,
                a_a.support_expired_date AS blade_support_expired_date, a_a.site_id AS blade_site_id
FROM all_assets aa
                LEFT JOIN manufacturer_model mm ON aa.manufacturer = mm.manufacturer AND 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
WHERE mm.hardware_type = 'chassis';

These are the 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 the same query on other systems and it takes only 5 seconds to fetch the result.

How do I optimize this query ?

Update (resolved)

Added indexes as suggested by experts here. Below is the execution plan after adding indexes.

enter image description here

Solution

  • Create indexes, non indexed reads are slower than index reads.

    To determine exactly what is causing you slow performance, the best tool is to use the "query plan analyzer":

    Have a look here: mySql performance explain

    Try creating indexes on the most obvious reads that will take place.Look at the fields that play a role when you join and also your where clause. If you have indexes on those fields your performance should increase, if non index reads were taking place.

    If this is still a problem it is best to see how mySQL is fetching the data, sometimes restructuring your data or even maybe altering the way you are queuing can give you better results.

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