title: mysql - query with multiple left joins takes too long
body: <p>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 <code>mysql workbench</code>. I even tried to execute it from <code>php</code> but that takes the same amount of time.</p>
  
  <pre><code>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';
  </code></pre>
  
  <p>These are the definition of tables being used:</p>
  
  <p><a href="https://i.stack.imgur.com/rt7p5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rt7p5.png" alt="enter image description here"></a></p>
  
  <p>Output of <code>EXPLAIN</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/jQ1dR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jQ1dR.png" alt="enter image description here"></a></p>
  
  <p>The query fetches data of each blade in each chassis.
  <strong>Executed the same query on other systems and it takes only 5 seconds to fetch the result.</strong></p>
  
  <p><strong>How do I optimize this query ?</strong></p>
  
  <p><strong>Update (resolved)</strong></p>
  
  <p>Added indexes as suggested by experts here.
  Below is the execution plan after adding indexes.</p>
  
  <p><a href="https://i.stack.imgur.com/J9Eyd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/J9Eyd.png" alt="enter image description here"></a></p>