Re: A very slow query on InnoDb 5.1.67
Thank you for the reply Peter,
I will ask for privilidges to update the server version / run a test on 5.6. Meanwhile, i have some more observations on the problem.
1.) The time this takes is relative to the amount of data in the query to start with. The query, if restricted to less rows by limiting sales order row id's (query at the bottom), takes considerable less time, but still takes a few seconds if not from cache.
2.) The time takes has increased considerably through the last month, way more than the amount of data. 30 days ago the query took 20seconds, now it takes 50 seconds. Apparently there are no changes to mysql configuration, so what could this be? (Im on a virtual private server).
3.) When i do explain on the query, i get a lot of "ALL" -type joins, and most joins on subqueries give me a using temporary; using filesort; in the extra column which are apparently bad.
***Quicker query***
SELECT SQL_CALC_FOUND_ROWS
FINALRESULT.sales_order_id,
FINALRESULT.sales_order_status,
DATE(FINALRESULT.sales_order_date) as sales_order_date,
FINALRESULT.method_shipment_id,
FINALRESULT.method_payment_id,
FINALRESULT.customer_firstname,
FINALRESULT.customer_lastname,
FINALRESULT.customer_company,
FINALRESULT.payment_method_name,
FINALRESULT.shipment_method_name,
#
# Sum part for result where actual amounts are compiled
ROUND(COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_total, 0)), 0), 2) as o_sum_total,
ROUND(COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_quantity, 0)), 0), 2) as o_sum_qty,
ROUND(COALESCE(SUM(FINALRESULT.sales_invoice_row_paid), 0), 2) as o_sum_paid,
ROUND(COALESCE(SUM(FINALRESULT.sales_invoice_row_total), 0), 2) as o_sum_invoiced,
ROUND(COALESCE(SUM(FINALRESULT.sales_shipment_row_qty), 0), 2) as o_sum_shipped,
#
# Percentage part for result where percentages are made.
# A Zero row and Zero shipped order will result in a 100% shipped order because there is nothing to ship.
# The same goes for invoices: if the row totals are zero and the invoiced amount is zero too, the invoiced amount is 100% because there is nothing to invoice.
# Invoiced percentage. Divide invoiced with total, and coalesce zero values to 1. Then multiply by 100.
ROUND(COALESCE(
COALESCE(SUM(FINALRESULT.sales_invoice_row_total), 0) /
COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_total, 0)), 0)
,1) * 100, 0) AS o_percentage_invoiced,
# Paid percentage. Divide paid with total, and coalesce zero values to 1. Then multiply by 100.
ROUND(COALESCE(
COALESCE(SUM(FINALRESULT.sales_invoice_row_paid), 0) /
COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_total, 0)), 0)
, 1) * 100, 0) AS o_percentage_paid,
# Shipped percentage. Divide sent rows by total rows, coalesce zero to 1, then multiply by 100.
ROUND(COALESCE(
COALESCE(SUM(FINALRESULT.sales_shipment_row_qty), 0) /
COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_quantity, 0)), 0)
, 1) * 100, 0) AS o_percentage_shipped
FROM
#
# The finalresult combines invoice row and order row subqueries into a result where the shipped, paid and invoiced amounts are
# returned per order row. No duplicate order rows are returned, so individual invoice and shipment rows are grouped by sales order rows
# in their respective subqueres.
# Customer, modules etc. 1on1 joins are also included, but they dont affect aggregate functions like sum like many to many joins.
#
(
# This query results in the FINALRESULT, which combines the queries into one result for clarity in final aggreagates grouped by order_id.
SELECT
INVOICES.sales_invoice_row_total,
INVOICES.sales_invoice_row_paid,
ORDERS_SHIPPED.sales_order_id,
ORDERS_SHIPPED.sales_order_status,
ORDERS_SHIPPED.sales_order_date,
ORDERS_SHIPPED.method_shipment_id,
ORDERS_SHIPPED.method_payment_id,
ORDERS_SHIPPED.sales_order_row_id,
ORDERS_SHIPPED.sales_order_row_quantity,
ORDERS_SHIPPED.sales_order_row_total,
ORDERS_SHIPPED.is_cancelled,
ORDERS_SHIPPED.customer_firstname,
ORDERS_SHIPPED.customer_lastname,
ORDERS_SHIPPED.customer_company,
ORDERS_SHIPPED.sales_shipment_row_qty,
ORDERS_SHIPPED.payment_method_name,
ORDERS_SHIPPED.shipment_method_name
FROM
#
# Invoices & their paid amounts query start
# This subquery returns unique sales order rows which have an invoice and a possible payment attached.
# This is joined to all sales order rows, giving a result where sales order rows seem to have an extra column with the paid and invoiced amount.
(
# Group by sales order row id
SELECT
INVOICES_PRE.sales_order_row_id,
SUM(INVOICES_PRE.sales_invoice_row_total) as sales_invoice_row_total,
SUM(INVOICES_PRE.sales_invoice_row_paid) as sales_invoice_row_paid
FROM
(
# Group by sales invoice row. This might still result in duplicate sales order rows if the order row
# has been invoiced on multiple invoices.
SELECT
rmm_sales_invoice_row.sales_order_row_id,
rmm_sales_invoice_row.sales_invoice_row_id,
rmm_sales_invoice_row.sales_invoice_row_total as sales_invoice_row_total,
COALESCE((rmm_sales_invoice_row.sales_invoice_row_total * INVOICES_PAID.paid / INVOICES_PAID.total), 0) AS sales_invoice_row_paid
FROM
(
# This query combines the remittance query union for credit and sales remittances.
SELECT IQ1.sales_invoice_id, SUM(paid1) as paid, total FROM
(
SELECT
rmm_sales_invoice.sales_invoice_id,
SUM(rmm_sales_invoice_row.sales_invoice_row_total) / COUNT(*) * COUNT(DISTINCT rmm_sales_invoice_row.sales_invoice_row_id) as total,
COALESCE(ROUND(SUM(rmm_sales_remittance.amount) / COUNT(*) * COUNT(DISTINCT rmm_sales_remittance.remittance_id), 2),0) AS paid1
FROM
rmm_sales_invoice LEFT JOIN rmm_sales_remittance ON
rmm_sales_invoice.sales_invoice_id = rmm_sales_remittance.sales_invoice_id
LEFT JOIN rmm_sales_invoice_row ON rmm_sales_invoice_row.sales_invoice_id = rmm_sales_invoice.sales_invoice_id
GROUP BY rmm_sales_invoice.sales_invoice_id
UNION
SELECT
rmm_sales_invoice.sales_invoice_id,
SUM(rmm_sales_invoice_row.sales_invoice_row_total) / COUNT(*) * COUNT(DISTINCT rmm_sales_invoice_row.sales_invoice_row_id) as total,
COALESCE(ROUND(SUM(-rmm_sales_remittance.amount) / COUNT(*) * COUNT(DISTINCT rmm_sales_remittance.remittance_id), 2),0) AS paid1
FROM
rmm_sales_invoice LEFT JOIN rmm_sales_remittance ON
rmm_sales_invoice.sales_invoice_id = rmm_sales_remittance.credit_invoice_id
LEFT JOIN rmm_sales_invoice_row ON rmm_sales_invoice_row.sales_invoice_id = rmm_sales_invoice.sales_invoice_id
GROUP BY rmm_sales_invoice.sales_invoice_id
) AS IQ1
GROUP BY IQ1.sales_invoice_id) AS INVOICES_PAID
JOIN
rmm_sales_invoice_row ON rmm_sales_invoice_row.sales_invoice_id = INVOICES_PAID.sales_invoice_id
) AS INVOICES_PRE
GROUP BY INVOICES_PRE.sales_order_row_id) AS INVOICES
RIGHT JOIN
# Sales order rows with shipped quantity
#
# Returns sales order rows where shipment rows are grouped by so the
# shipped quantity per row is already taken care of
(
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
AND rmm_sales_order.sales_order_id IN('409', '441', '908', '1010', '1259', '1520', '1596', '1625', '1703', '1714', '1798', '1836', '1840', '1889', '2069', '2179', '2289', '2430', '2449', '2493', '2499', '2570', '2815', '3093', '3100', '3228', '3417', '3483', '3490', '3510', '3601', '3887', '3983', '3988', '4134', '4190', '4235', '4321', '4353', '4454', '4505', '4535', '4537', '4649', '4720', '4777', '4874', '4976', '4998', '5024', '5025', '5054', '5079', '5092', '5094', '5113', '5135', '5150', '5262', '5312', '5333', '5337', '5403', '5432', '5459', '5480', '5492')
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
) AS ORDERS_SHIPPED
ON ORDERS_SHIPPED.sales_order_row_id = INVOICES.sales_order_row_id
) AS FINALRESULT
GROUP BY FINALRESULT.sales_order_id
ORDER BY FINALRESULT.sales_order_id DESC LIMIT 0, 30