优化使用两个子查询的mysql查询并且为3个表左边连接需要更好的解决方案

I have a big query which is joining 3 tables and have two subqueries I want to optimise it. I was thinking to extract subqueries in to seprate query but for that I have to use an extra for loop and have to run query inside for loop for n number of time. Is there any better way to find data ? want to optimize my solution . Please provide me good solution/suggestion

Below is my query.

    SELECT `e`.`id`
    ,(SELECT sum(sl.amount)
      FROM `sales` AS `sl`
      LEFT JOIN `trans` AS `t` ON t.id = sl.transId
      WHERE (sl.entity_id = e.id)
          AND (DATE_FORMAT(t.accounting_date, '%Y-%m')) = '2015-02') AS `2015-02`
    ,(SELECT sum(sl.amount)
      FROM `sales` AS `sl`
      LEFT JOIN `trans` AS `t` ON t.id = sl.transId
      WHERE (sl.entity_id = e.id)
          AND (DATE_FORMAT(t.accounting_date, '%Y-%m'))) AS `2015-01`
    ,`e`.`forenames`
    , `e`.`surname`
    , `e`.`company_name`
    , `e`.`address1`
    ,`e`.`address2`
    , `e`.`phone1`
    ,`e`.`postcode`
    , eassoc.associated_entity_id IS NULL AS `is_parent`
    ,`ent`.`accountPersonManager`
FROM `entity` AS `e`
LEFT JOIN `entity_link` AS `eassoc` ON eassoc.linkentity_id = e.id 
LEFT JOIN `entities` AS `ent` ON ent.entity_id = e.id 
WHERE (e.archived = 0
    OR e.archived IS NULL) 
GROUP BY `e`.`id`
ORDER BY `2015-02` DESC LIMIT 75

Using different queries and loops is not a solution to optimize the job you want to achieve. Based on the few information you provided to us, I'll suggest you to avoid subqueries and use jointures instead (the subqueries are executed for EVERY rows of your data set and the result is then grouped).

Try this query:

SELECT `e`.`id`
    ,MAX(CASE
            WHEN slbymonth = '2015-02' THEN slbymonth.total
            ELSE 0
         END) AS `2015-02`
    ,MAX(CASE
            WHEN slbymonth = '2015-01' THEN slbymonth.total
            ELSE 0
         END) AS `2015-01`
    ,`e`.`forenames`
    , `e`.`surname`
    , `e`.`company_name`
    , `e`.`address1`
    ,`e`.`address2`
    , `e`.`phone1`
    ,`e`.`postcode`
    , eassoc.associated_entity_id IS NULL AS `is_parent`
    ,`ent`.`accountPersonManager`
FROM `entity` AS `e`
LEFT JOIN (SELECT sl.entity_id
               ,DATE_FORMAT(t.accounting_date, '%Y-%m') AS `month`
               ,SUM(sl.amount) AS `total`
           FROM `sales` AS `sl`
           INNER JOIN `trans` AS `t` ON T.id = sl.transId
           GROUP BY sl.entity_id, DATE_FORMAT(t.accounting_date, '%Y-%m')) AS `slbymonth` ON slbymonth.entity_id = e.id
                                                                                              AND slbymonth.month IN ('2015-01', '2015-02')
LEFT JOIN `entity_link` AS `eassoc` ON eassoc.linkentity_id = e.id 
LEFT JOIN `entities` AS `ent` ON ent.entity_id = e.id 
WHERE IFNULL(e.archived, 0) = 0
GROUP BY `e`.`id`, `e`.`forenames`, ...
ORDER BY `2015-02` DESC LIMIT 75

Since we have no information about your tables structure, the proposed query is based on my interpretation.

I removed both subqueries and used a new jointure on a custom query that is an aggregation of sales per months for each entity. To optimize the jointure I added a condition in order to keep only the months '2015-01' and '2015-02'.

Hope this will help you, don't hesitate to provide us more information in order to get better advices from us.