MySQL Forums
Forum List  »  InnoDB

A very slow query on InnoDb 5.1.67
Posted by: Willem van Schevikhoven
Date: April 17, 2013 01:51AM

Hello everyone,
I have an InnoDB database which we use on our webshop written in PHP. MySql version 5.1.67.

The problem is a very slow query, and overall database performance. The query in question retrieves sales orders from the db based on properties like percentage invoiced, shipped etc. , this said, the query is quite long consisting of several joins and subqueries. I will include the query at the end of the message.

The reason I am writing this message is, that when we switched to a more powerful server which now has apache and mysql on the same machine, the execution time of this query has slowly crept from 20 seconds to 49 seconds, while overall performance of the site is a bit better or the same.

This query only takes a long time after the database has been updated. A second query uses the caches and the data is retrieved in a blink of an eye. The problem is that the orders table is the most frequently updated table and every other query takes 49 seconds. This is starting to be very hard to tolerate.

The data is also quite long, so providing test data without actually dumping the database is next to impossible. Would uploading a dump (with important information replaced) be a good idea?

Table sizes (rows):
rmm_sales_order: 5357
rmm_sales_order_row: 18885
rmm_sales_invoice:3016
rmm_sales_invoice_row: 7266
rmm_sales_shipment: 2846
rmm_sales_shipment_row:9870
rmm_customers:3578

Other tables involved in the query have < 100 rows. So in other words, the amount of data is very small and shouldn’t be the direct cause of this issues if I am correct.
I have looked at an explain for the query, but to be honest I am not experienced enough to determine if things are going right or wrong.

***So, what additional information should I provide in order to start getting to the bottom of this? I am not sure what the problem is: mysql settings or query design (quite heavy), or perhaps both. I am quite unexperienced, i have only built this webshop as a side project at work***

***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


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

Options: ReplyQuote


Subject
Views
Written By
Posted
A very slow query on InnoDb 5.1.67
2869
April 17, 2013 01:51AM


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.