SELECT sfo.entity_id AS order_id, CASE WHEN LOCATE('delivered',
GROUP_CONCAT(sfo_1.status)) THEN 'repeat-a' WHEN GROUP_CONCAT(sfo_1.status
) <> '' THEN 'repeat' END AS `repeat`,
IF(LOCATE('-R', sfo.increment_id), 'alternate', NULL) AS alternate,
sfq.orig_order_id, sfo.purchase_from, sfo.customer_email,
sfo.status, sfoa.telephone, sfo.relation_parent_id, sfo.increment_id,
sfoi.utm_source
FROM sales_flat_order sfo
INNER JOIN sales_flat_order_address sfoa ON sfo.entity_id=sfoa.parent_id
LEFT JOIN sales_flat_order_address sfoa_1 ON (sfoa.email=sfoa_1.email
OR sfoa.telephone=sfoa_1.telephone
)
AND sfoa_1.parent_id < sfo.entity_id
AND sfoa_1.address_type = 'billing'
LEFT JOIN sales_flat_quote sfq ON sfo.quote_id=sfq.entity_id
LEFT JOIN sales_flat_order AS sfo_1 ON sfoa_1.parent_id = sfo_1.entity_id
LEFT JOIN sales_flat_order_info AS sfoi ON sfo.entity_id = sfoi.order_id
WHERE sfoa.address_type='billing'
AND sfo.entity_id IN (439461,439462,439463,439464,439465,
439466,439467,439468,439469,439470,439471,439472,439473,
439474,439475,439476,439477,439478,439479, 439480,439481,
439482,439483,439484,439485,439486,439489,439492
)
GROUP BY sfo.entity_id
* Please see how many rows you get without the GROUP BY. Often it is a huge number. That is the query (with all its JOINs) first expands like crazy, then shrinks because of the GROUP BY. If so, we can look into avoiding the expansion.
* Please try this: Simplify the query by removing sales_flat_quote, sales_flat_order, and sales_flat_order_info.
Then see if the rest of the query runs almost as slow. If so, then we should focus on the sfoa_1 join which seems to be doing a "groupwise max" (or min?). That is a poorly optimized construct.
* Remove LEFT unless you need it.
* See if replacing "sfo.orig_order_id" with "( SELECT orig_order_id FROM sales_flat_quote WHERE sfo.quote_id=entity_id ) AS orig_order_id" and remove "LEFT JOIN sales_flat_quote sfq ON sfo.quote_id=sfq.entity_id".
* Ditto with the reference to sfoi.