MySQL Forums
Forum List  »  InnoDB

Re: A very slow query on InnoDb 5.1.67
Posted by: Willem van Schevikhoven
Date: May 23, 2013 03:38AM

Hello,

Updating to 5.6 solved many performance issues, and the general speed increase for our site is quite nice. Now the query in question completes in around 1 to 2 seconds which is way more acceptable. Thank you very much for helping me on this.

The update to 5.6 however introduced some rather strange behaviour on the queries, and this is now a problem.

The following query, depending on the marked join type and marked group by returns different results, when they should actually return the same results. STRAIGHT_JOIN didnt have an effect.

The problem is, that the subquery (resulting in the table INVOICES), returns one row per sales order row. One would expect, that the invoice row total would then show up correctly in the query result when joined, but is is empty if i use LEFT JOIN, and it is correctly populated if i use an INNER JOIN.
If invoices returned more than one row per sales order row, this would be an expected behaviour (depending how the subquery is ordered), but now i dont understand what is wrong.
The sales_invoice_total field is also correctly populated with the subquery data if i remove the GROUP BY at the end (which in my test case doesnt do anything real, as the sample data has 1 shipment row per sales order row, one for each.). While removing the group by, i can also use LEFT JOIN instead if an INNER JOIN and get the correct results, so removing/changing either one cures the problem, but wont work on a wider dataset where sales order rows might not be invoiced or shipped, and in fact need a left join AND a group by at the end.

I am guessing the optimizer is playing tricks and the execution order changes, perhaps so that the subquery data is not grouped by correctly, but for the time being i havent figured out why this isnt working as it's in my uneducated opinion perfectly valid and logical.
Is there a solution for this, or perhaps something wrong with this query? Something in mysql settings which could cause this?
Please also note, that this worked as expected on mysql 5.1, and only started troubling me after the update to 5.6. Reverting back is in my opinion no option, as the performance gains of 5.6 on my database are huge.
SELECT
INVOICES.sales_invoice_row_total,
INVOICES.sales_invoice_row_paid,
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,

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
AND rmm_sales_order.sales_order_id IN('4984')

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
#
# Changing this to an INNER JOIN returns the correct results. The subquery only returns one row per salesorder row.
#
LEFT JOIN

# 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,
rmm_sales_invoice_row.sales_invoice_row_total * INVOICES_PAID.paid / INVOICES_PAID.total 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,
SUM(rmm_sales_remittance.amount) / COUNT(*) * COUNT(DISTINCT rmm_sales_remittance.remittance_id) 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,
SUM(-rmm_sales_remittance.amount) / COUNT(*) * COUNT(DISTINCT rmm_sales_remittance.remittance_id) 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
ON rmm_sales_order_row.sales_order_row_id = INVOICES.sales_order_row_id

#
# The Group by is needed, since an order row (for 3 items. f.ex) can be shipped on two different shipments (1 + 2 items on separate rows).
#
GROUP BY rmm_sales_order_row.sales_order_row_id

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: A very slow query on InnoDb 5.1.67
1417
May 23, 2013 03:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.