MySQL Forums
Forum List  »  InnoDB

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

Hello Peter, Rick,

I've ran the subquery on it's own, and it does return all the correct results. When you look at the query, you see that the subquery actually doesnt change, and always returns all invoice rows with their paid amounts, total and the according sales order row id.
I didnt put them into a temptable, but making the query confirms that the subquery on it's own returns what it's supposed to.

The outer query has 11 columns which are not aggregated, two of which come from the subquery, and others from the sales order or sales order rows. The 9 remaining (non aggregated and not from the subquery) are what they are supposed to be and cause no bugs. I have included a modified query at the bottom, which does return the same strange results, has less columns and can also be cured with the replacement of group by or left join.

The following also produces the same strange results, and can be cured with replacing ANY left join or group by at the end (which in a real situation will not return correct rows.). So to be more exact, the subquery can be left joined, and changing ANY of the left join to a join seems to solve the mystery. This in my opinion would even more suggest some settings are off or there is a bug in the release. What are your opinions?
SELECT
INVOICES.sales_invoice_row_total,
INVOICES.sales_invoice_row_paid,
rmm_sales_order_row.sales_order_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_order_row


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
HAVING rmm_sales_order_row.sales_order_id IN('4984')

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: A very slow query on InnoDb 5.1.67
1064
May 24, 2013 03:20AM


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.