Hello again,
I've made some pretty good improvements, an now i'm at 11 to 14 seconds execution time, still on the 5.1 version.
1.) I increased the memory from 1gb to 4gb, and added a second core totalling a two core setup now. This seemed to lower the average times to around 30-ish, but i could still see a lot of 50 second times.
2.) I removed one subquery from the joins, and this made a huge improvement all the way upto 11 to 14 seconds execution time. The rest of the query still needs to be looked at, since i'm eager to see if i can remove more subqueries especially from joins. The problem is the aggregate(?) functions which need nested group by statements. There is some play with SUM(x / COUNT(*) * DISTINCT COUNT(*)) #(The syntax here might be off, but you get the idea). When there are more and more tables, and more tables which potentially cause duplicate rows for the sum, the previous statements become more and more complex and prone to errors.
Below is the altered code, which now runs a lot faster. Like i said, more opimization on the query it's self is needed before i'll go into tuning the server more. If there are any pointers on the query it's self, they are very welcome =)
#
# NOTE: This is not the complete query, see 1st post for the complete query which took 50 seconds.
#
#
# Original part of the query
#
SELECT
rmm_sales_order.sales_order_id,
rmm_sales_order.sales_order_status,
rmm_sales_order.sales_order_date,
rmm_sales_order.method_shipment_id,
rmm_sales_order.method_payment_id,
rmm_sales_order_row.sales_order_row_id,
rmm_sales_order_row.sales_order_row_quantity,
rmm_sales_order_row.sales_order_row_total,
rmm_sales_order_row.is_cancelled,
rmm_customers.customer_firstname,
rmm_customers.customer_lastname,
rmm_customers.customer_company,
SHIPMENTS.sales_shipment_row_qty,
rmm_modules_payment.method_name as payment_method_name,
rmm_modules_shipment.method_name as shipment_method_name
FROM
rmm_sales_order JOIN rmm_sales_order_row ON
rmm_sales_order.sales_order_id = rmm_sales_order_row.sales_order_id
LEFT JOIN rmm_customers ON rmm_customers.customer_id = rmm_sales_order.billing_customer_id
LEFT JOIN rmm_modules_payment
ON rmm_sales_order.method_payment_id = rmm_modules_payment.method_id
LEFT JOIN rmm_modules_shipment
ON rmm_sales_order.method_shipment_id = rmm_modules_shipment.method_id
LEFT JOIN
# Sales shipments and their rows query which returns sales order rows with their aggregated (SUM) shipped quantity.
# Sales shipment rows are grouped by the sales order row id, so no duplicate sales order rows are returned.
(
SELECT
rmm_sales_shipment_row.sales_order_row_id,
SUM(IF(rmm_sales_shipment.sales_shipment_type='return', -rmm_sales_shipment_row.sales_shipment_row_qty, rmm_sales_shipment_row.sales_shipment_row_qty)) AS sales_shipment_row_qty
FROM
rmm_sales_shipment JOIN rmm_sales_shipment_row ON rmm_sales_shipment.sales_shipment_id = rmm_sales_shipment_row.sales_shipment_id
GROUP BY rmm_sales_shipment_row.sales_order_row_id
) AS SHIPMENTS
ON SHIPMENTS.sales_order_row_id = rmm_sales_order_row.sales_order_row_id
#
# Altered part
#
SELECT
rmm_sales_order.sales_order_id,
rmm_sales_order.sales_order_status,
rmm_sales_order.sales_order_date,
rmm_sales_order.method_shipment_id,
rmm_sales_order.method_payment_id,
rmm_sales_order_row.sales_order_row_id,
rmm_sales_order_row.sales_order_row_quantity,
rmm_sales_order_row.sales_order_row_total,
rmm_sales_order_row.is_cancelled,
rmm_customers.customer_firstname,
rmm_customers.customer_lastname,
rmm_customers.customer_company,
rmm_modules_payment.method_name as payment_method_name,
rmm_modules_shipment.method_name as shipment_method_name,
SUM(IF(rmm_sales_shipment.sales_shipment_type='return', -rmm_sales_shipment_row.sales_shipment_row_qty, rmm_sales_shipment_row.sales_shipment_row_qty)) AS sales_shipment_row_qty
FROM
rmm_sales_order_row JOIN rmm_sales_order
ON rmm_sales_order.sales_order_id = rmm_sales_order_row.sales_order_id
LEFT JOIN rmm_customers
ON rmm_customers.customer_id = rmm_sales_order.billing_customer_id
LEFT JOIN rmm_modules_payment
ON rmm_sales_order.method_payment_id = rmm_modules_payment.method_id
LEFT JOIN rmm_modules_shipment
ON rmm_sales_order.method_shipment_id = rmm_modules_shipment.method_id
LEFT JOIN rmm_sales_shipment_row
ON rmm_sales_order_row.sales_order_row_id = rmm_sales_shipment_row.sales_order_row_id
LEFT JOIN rmm_sales_shipment
ON rmm_sales_shipment.sales_shipment_id = rmm_sales_shipment_row.sales_shipment_id
GROUP BY rmm_sales_order_row.sales_order_row_id