Re: query performance optimization
Hi,
Some other comments/question on query:
Do you really need SQL_CALC_FOUND_ROWS? It will generally make the query slower since it will have to compute the entire result, not just the number of rows specified by LIMIT. However, not sure how much it costs in this case since you are sorting on a different column from what you are grouping on. Hence, you will have to do a full join to produce the grouping anyway.
(SAM.admin_user_id IS NULL OR SAM.admin_user_id='24577'): I guess the IS NULL part was added in order to cater for NULL values generated by LEFT JOIN for rows without match. If so, you should rather move "SAM.admin_user_id='24577'" to the ON clause of the LEFT JOIN. OR clauses tend to prevent usage of indexes.
FIND_IN_SET('PENDING' , states): It seems strange to concat states in select list and then unwrap it HAVING clause. Cannot this condition be moved to WHERE clause as "ts.state='PENDING'?
You are ordering on a different column than what you are displaying (SFOI.created_at versus SFO.created_at). Is this intentional? Note that SFOI.created_at will be an arbitrary value from potentially multiple rows that match the condition SFO.entity_id=SFOI.order_id.
You did not include information about what columns of other tables than sales_flat_order are indexed, except that you say that all join fields are indexed. You should also consider adding indexes for columns that take part in WHERE conditions etc. Maybe combined indexes on these columns and join key could help, too.
I do not know how selective the conditions on CPEI.entity_type_id and CPEI.attribute_id, unless you already have it, you should consider an index on (entity_type_id, attribute_id) that does not include product_id as first part. With such an index, MySQL may be able to restrict the number of rows to access in sales_flat_order to those that match the specified entity_type_id and attribute_id. I suggest you also move "CPEI.entity_type_id = 4" to WHERE clause since it is not really a join condition.
Another tip is to increase setting of innodb_stats_persistent_sample_pages and run ANALYZE on the involved tables. Maybe more accurate statistics can improve the query plan.
When joining a large number of tables, the optimization time may be large compared to execution time. Does EXPLAIN on this query also take long?
You can also try to set optimizer_prune_level to 0 to see if that will give a different/better plan. Note that optimization time will increase in that case since more plans will be considered.
If nothing of above helps, you could try to force another join plan with the STRAIGHT_JOIN hint. For example, if the CPEI conditions will significantly limit the number of rows to be considered, you could force a join order that starts with CPEI by rewriting query to something like this:
FROM catalog_product_entity_int CPEI sales_flat_order AS SFO
STRAIGHT_JOIN sales_flat_order_item SFOI ON CPEI.entity_id = SFOI.product_id
INNER JOIN sales_flat_order SFO ON SFO.entity_id=SFOI.order_id
...
Similarly, you could try forcing SSI or ts to be processed early if the conditions on SSI.is_eligible_sc or ts.state is very selective.
If none of the above helps, I would need more info to give more advice. The content of mysql.innodb_table_stats and mysql.innodb_index_stats would be helpful to see cardinality of indexed columns. Also, the optimizer_trace for the query would be interesting.