HEllo,
I think there is really something going on here, which needs a bit attention.
The following queries have one difference, LEFT JOIN vs JOIN. The explains are first, because they are the most important.
See how the order of the JOIN is different, and the faster one uses proper indexes. The slower one with left join has a possible key (primary), but doesnt use it.
This troubles me, since LEFT JOIN and JOIN here are 1 to 1, the LEFT join is meant to simply add the invoiced amounts to the order rows which are mated together by sales_order_row_id. There is something about the optimizer needing to look for null values etc, but can it really be this big a difference on performance, as we are only talking about a result which is 3x bigger and a is still a huge performance hit.
#
# SLOW QUERY WITH LEFT JOIN
#
+----+--------------+-----------------------+-------+---------------------------------------------+---------------------------------------------+---------+-------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-----------------------+-------+---------------------------------------------+---------------------------------------------+---------+-------------------------------------------+------+---------------------------------+
*| 1 | PRIMARY | rmm_sales_order | ALL | PRIMARY | NULL | NULL | NULL | 5127 | Using temporary; Using filesort |
*| 1 | PRIMARY | rmm_sales_order_row | ref | fk_rmm_sales_order_row_rmm_sales_order | fk_rmm_sales_order_row_rmm_sales_order | 4 | racefi.rmm_sales_order.sales_order_id | 1 | |
*| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 6874 | |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 7439 | Using temporary; Using filesort |
| 3 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 3089 | |
| 3 | DERIVED | rmm_sales_invoice_row | ref | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | 4 | INVOICES_PAID.sales_invoice_id | 1 | |
| 4 | DERIVED | <derived5> | ALL | NULL | NULL | NULL | NULL | 6122 | Using temporary; Using filesort |
| 5 | DERIVED | rmm_sales_invoice | index | NULL | PRIMARY | 4 | NULL | 3175 | Using index |
| 5 | DERIVED | rmm_sales_remittance | ref | sales_invoice_id | sales_invoice_id | 4 | racefi.rmm_sales_invoice.sales_invoice_id | 1 | |
| 5 | DERIVED | rmm_sales_invoice_row | ref | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | 4 | racefi.rmm_sales_invoice.sales_invoice_id | 1 | |
| 6 | UNION | rmm_sales_invoice | index | NULL | PRIMARY | 4 | NULL | 6 | Using index |
| 6 | UNION | rmm_sales_remittance | ref | credit_invoice_id | credit_invoice_id | 5 | racefi.rmm_sales_invoice.sales_invoice_id | 457 | |
| 6 | UNION | rmm_sales_invoice_row | ref | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | 4 | racefi.rmm_sales_invoice.sales_invoice_id | 1 | |
| NU | UNION RESULT | <union5,6> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+-----------------------+-------+---------------------------------------------+---------------------------------------------+---------+-------------------------------------------+------+---------------------------------+
#
# Fast query with JOIN
#
+----+--------------+-----------------------+--------+------------------------------------------------+---------------------------------------------+---------+-------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-----------------------+--------+------------------------------------------------+---------------------------------------------+---------+-------------------------------------------+------+---------------------------------+
*| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 6874 | Using temporary; Using filesort |
*| 1 | PRIMARY | rmm_sales_order_row | eq_ref | PRIMARY,fk_rmm_sales_order_row_rmm_sales_order | PRIMARY | 4 | INVOICES.sales_order_row_id | 1 | |
*| 1 | PRIMARY | rmm_sales_order | eq_ref | PRIMARY | PRIMARY | 4 | racefi.rmm_sales_order_row.sales_order_id | 1 | |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 7439 | Using temporary; Using filesort |
| 3 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 3089 | |
| 3 | DERIVED | rmm_sales_invoice_row | ref | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | 4 | INVOICES_PAID.sales_invoice_id | 1 | |
| 4 | DERIVED | <derived5> | ALL | NULL | NULL | NULL | NULL | 6122 | Using temporary; Using filesort |
| 5 | DERIVED | rmm_sales_invoice | index | NULL | PRIMARY | 4 | NULL | 3175 | Using index |
| 5 | DERIVED | rmm_sales_remittance | ref | sales_invoice_id | sales_invoice_id | 4 | racefi.rmm_sales_invoice.sales_invoice_id | 1 | |
| 5 | DERIVED | rmm_sales_invoice_row | ref | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | 4 | racefi.rmm_sales_invoice.sales_invoice_id | 1 | |
| 6 | UNION | rmm_sales_invoice | index | NULL | PRIMARY | 4 | NULL | 6 | Using index |
| 6 | UNION | rmm_sales_remittance | ref | credit_invoice_id | credit_invoice_id | 5 | racefi.rmm_sales_invoice.sales_invoice_id | 457 | |
| 6 | UNION | rmm_sales_invoice_row | ref | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | fk_rmm_sales_invoice_row_rmm_sales_invoice1 | 4 | racefi.rmm_sales_invoice.sales_invoice_id | 1 | |
| NL | UNION RESULT | <union5,6> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+-----------------------+--------+------------------------------------------------+---------------------------------------------+---------+-------------------------------------------+------+---------------------------------+
#
# Query 1, this is with LEFT JOIN, and this is very slow, 12 seconds, returns 19k rows
#
SELECT SQL_NO_CACHE
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
FROM
rmm_sales_order_row JOIN rmm_sales_order
ON rmm_sales_order.sales_order_id = rmm_sales_order_row.sales_order_id
#*******************CHANGED HERE******************
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
GROUP BY rmm_sales_order_row.sales_order_row_id
#
# Query 2 with a JOIN which is 0.5 sec. returns 6.5k rows.
#
SELECT SQL_NO_CACHE
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
FROM
rmm_sales_order_row JOIN rmm_sales_order
ON rmm_sales_order.sales_order_id = rmm_sales_order_row.sales_order_id
#*******************CHANGED HERE******************
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
GROUP BY rmm_sales_order_row.sales_order_row_id