title: Improving query performance in MYSQL with a lot of subqueries
body: <p>I am pretty new to MYSQL and in general writing queries.
  the most important things in this query is cycle_count_id and location. all information should be based on this two factors.
  now, how can I improve this query:</p>
  
  <pre><code>SELECT c2.code_cycle_count, c2.location, c2.last_cyclecount, c2.second_recent_cyclecount ,i.uid,c2.po_number, i.cost, i.uid
  
  
     , (SELECT
        CASE WHEN MAX(cc.updated_at) = MAX(ccc.updated_at) THEN u.username ELSE NULL END
        from oms_live_ir.wms_cycle_count ccc
      INNER JOIN oms_live_ir.ims_user u ON u.id_user=ccc.fk_user
        WHERE ccc.fk_location = cc.fk_location AND ccc.id_cycle_count=cc.id_cycle_count) AS cycle_count_user
  
  
  
     , (SELECT  COUNT(cci.fk_cycle_count_item_status)
  
      from oms_live_ir.wms_cycle_count_item cci
      WHERE cci.fk_cycle_count_item_status = 1 AND cc.id_cycle_count=cci.fk_cycle_count) AS location_updated
  
    , (SELECT  COUNT(cci.fk_cycle_count_item_status)
      from oms_live_ir.wms_cycle_count_item cci
      WHERE cci.fk_cycle_count_item_status = 2 AND cc.id_cycle_count=cci.fk_cycle_count) AS status_updated
  
  
    , (SELECT  COUNT(cci.fk_cycle_count_item_status)
      from oms_live_ir.wms_cycle_count_item cci
      WHERE cci.fk_cycle_count_item_status = 4 AND cc.id_cycle_count=cci.fk_cycle_count) AS found
  
    , (SELECT  COUNT(cci.fk_cycle_count_item_status)
      from oms_live_ir.wms_cycle_count_item cci
      WHERE cci.fk_cycle_count_item_status = 6 AND cc.id_cycle_count=cci.fk_cycle_count) AS lost
  
    , c5.id_pick, c5.date_pick
  
  
    FROM oms_live_ir.wms_cycle_count cc
    INNER JOIN oms_live_ir.wms_inventory i on i.fk_location=cc.fk_location
  
  
  
    INNER join
      (SELECT l.description AS location
    ,MAX(cccc.id_cycle_count) AS code_cycle_count
    ,MAX(cccc.updated_at) AS last_cyclecount, i.po_number
    ,(SELECT MAX(c1.updated_at) FROM oms_live_ir.wms_cycle_count c1 WHERE c1.updated_at&lt;&gt;MAX(cccc.updated_at)
    AND c1.fk_location=cccc.fk_location) as second_recent_cyclecount
  
     FROM oms_live_ir.wms_cycle_count cccc
    INNER JOIN oms_live_ir.wms_inventory i ON i.fk_location=cccc.fk_location
    INNER JOIN oms_live_ir.ims_location l ON l.id_location=cccc.fk_location
  
     WHERE year(cccc.updated_at)&gt;=2018 AND month(cccc.updated_at)&gt;=1
     AND LEFT(i.po_number,2) LIKE 'M1%' or LEFT(i.po_number,2) LIKE 'S1%'
       GROUP by cccc.fk_location
     ) c2 on c2.code_cycle_count= cc.id_cycle_count
  
  INNER JOIN
  (SELECT  cc5.fk_location, 
            (SELECT CASE WHEN ih2.updated_at=MIN(ih2.updated_at) THEN ih2.sales_order_item_id ELSE NULL END
             FROM oms_live_ir.wms_cycle_count cc2
              INNER JOIN oms_live_ir.wms_inventory_history ih2 ON ih2.fk_location=cc2.fk_location
              WHERE ih2.sales_order_item_id=ih5.sales_order_item_id AND ih2.fk_location=cc5.fk_location AND cc2.id_cycle_count=cc5.id_cycle_count
              AND year(ih2.updated_at)&gt;=2018 AND MONTH(ih2.updated_at)&gt;=1 AND ih2.sales_order_item_id IS NOT NULL 
              GROUP BY ih2.id_inventory) id_pick
            ,(SELECT CASE WHEN ih2.updated_at=MIN(ih2.updated_at) THEN ih5.updated_at ELSE NULL END
              FROM oms_live_ir.wms_cycle_count cc2
              INNER JOIN oms_live_ir.wms_inventory_history ih2 ON ih2.fk_location=cc2.fk_location
              WHERE ih2.sales_order_item_id=ih5.sales_order_item_id AND ih2.fk_location=cc5.fk_location AND cc2.id_cycle_count=cc5.id_cycle_count
              AND year(ih2.updated_at)&gt;=2018 AND MONTH(ih2.updated_at)&gt;=1 AND ih2.sales_order_item_id
              GROUP BY ih2.id_inventory) date_pick
  
            FROM oms_live_ir.wms_cycle_count cc5
            INNER JOIN oms_live_ir.wms_inventory_history ih5 ON ih5.fk_location=cc5.fk_location
   WHERE year(ih5.updated_at)&gt;=2018 AND MONTH(ih5.updated_at)&gt;=1 AND ih5.sales_order_item_id IS NOT NULL) c5 ON c5.fk_location=cc.fk_location
  
  
  
      WHERE year(cc.updated_at)&gt;=2017 AND month(cc.updated_at)&gt;=1
    AND LEFT(i.po_number,2) LIKE 'M1%' or LEFT(i.po_number,2) LIKE 'S1%'
  group by c2.code_cycle_count, c2.location;
  </code></pre>
  
  <p>(Note: I do not have access to create temporary tables or create index for tables.)</p>
  
  <p>Thanks in advance.</p>