Skip navigation links

MySQL Forums :: InnoDB :: A very slow query on InnoDb 5.1.67


Advanced Search

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 1100 Willem van Schevikhoven 04/17/2013 01:51AM
Re: A very slow query on InnoDb 5.1.67 459 Peter Brawley 04/17/2013 09:53AM
Re: A very slow query on InnoDb 5.1.67 641 Willem van Schevikhoven 04/17/2013 02:24PM
Re: A very slow query on InnoDb 5.1.67 496 Peter Brawley 04/17/2013 04:48PM
Re: A very slow query on InnoDb 5.1.67 752 Willem van Schevikhoven 04/18/2013 01:25AM
Re: A very slow query on InnoDb 5.1.67 484 Peter Brawley 04/18/2013 10:25AM
Re: A very slow query on InnoDb 5.1.67 518 Willem van Schevikhoven 04/18/2013 10:51AM
Re: A very slow query on InnoDb 5.1.67 466 Peter Brawley 04/18/2013 11:36AM
Re: A very slow query on InnoDb 5.1.67 425 Willem van Schevikhoven 04/18/2013 12:03PM
Re: A very slow query on InnoDb 5.1.67 460 Rick James 04/18/2013 08:29PM
Re: A very slow query on InnoDb 5.1.67 447 Peter Brawley 04/18/2013 10:53PM
Re: A very slow query on InnoDb 5.1.67 444 Willem van Schevikhoven 04/23/2013 02:24AM
Re: A very slow query on InnoDb 5.1.67 428 Rick James 04/23/2013 08:17AM
Re: A very slow query on InnoDb 5.1.67 380 Willem van Schevikhoven 04/26/2013 07:06AM
Re: A very slow query on InnoDb 5.1.67 492 Willem van Schevikhoven 04/27/2013 08:32AM
Re: A very slow query on InnoDb 5.1.67 565 Rick James 04/27/2013 09:47PM
Re: A very slow query on InnoDb 5.1.67 341 Willem van Schevikhoven 05/23/2013 03:38AM
Re: A very slow query on InnoDb 5.1.67 347 Rick James 05/23/2013 08:27AM
Re: A very slow query on InnoDb 5.1.67 344 Peter Brawley 05/23/2013 10:41AM
Re: A very slow query on InnoDb 5.1.67 288 Rick James 05/23/2013 08:25PM
Re: A very slow query on InnoDb 5.1.67 334 Willem van Schevikhoven 05/24/2013 03:20AM
Re: A very slow query on InnoDb 5.1.67 355 Willem van Schevikhoven 05/24/2013 03:22AM
Re: A very slow query on InnoDb 5.1.67 311 Peter Brawley 05/24/2013 11:13AM
Re: A very slow query on InnoDb 5.1.67 409 Willem van Schevikhoven 05/24/2013 01:43PM
Re: A very slow query on InnoDb 5.1.67 353 Peter Brawley 05/24/2013 02:03PM
Re: A very slow query on InnoDb 5.1.67 364 Willem van Schevikhoven 05/24/2013 10:43PM
Re: A very slow query on InnoDb 5.1.67 355 Peter Brawley 05/26/2013 09:40PM


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.