Muhammad Asif Raza Muhammad Asif Raza - 5 months ago 99
SQL Question

How to improve query execution time?

I have a rather complex query, which takes 1-2 minutes to execute. Is there a way to improve execution time?

Here is the query:

select o.orders_id, o.customers_id, o.customers_name, s.orders_status_name,
ot.text as order_total, ot.value, DATEDIFF(NOW(), payment_data_read_status) as numDaysLeft,
( SELECT ifnull(sum(op.paid_amount), 0)
from orders_payment op
where op.orders_id=o.orders_id
AND op.confirm_payment='1'
) as paid_total
from orders o, orders_total ot, orders_status s
where o.orders_id = ot.orders_id
and ot.class = 'ot_total'
and o.orders_status = s.orders_status_id
and s.language_id = '1'
AND ROUND(ot.value,2) != ROUND(
( SELECT ifnull(sum(op.paid_amount),0)
from orders_payment op
where op.orders_id=o.orders_id
AND op.confirm_payment='1'
), 2)


Query Explanation



enter image description here

Some details




number of records in orders = 7321

number of records in orders_total = 22167

number of records in orders_payment= 12038

number of records in orders_status= 9


orders_id column is auto increment in orders table. Firstly I thought to index orders_id column in orders table but as it is primary so I don't think it will work.

EDITS
Error

enter image description here

Answer Source

I find that nested queries are not necessarily bad, but I try to avoid putting them in the select list. This is my suggestion:

select
    o.orders_id,
    o.customers_id,
    o.customers_name,
    s.orders_status_name, 
    ot.text as order_total, 
    ot.value, 
    datediff(now(), payment_data_read_status) as numdaysleft, 
    ifnull(op.paid_total, 0) paid_total
from
    orders o
    join
    orders_total ot
    on o.orders_id = ot.orders_id 
    join
    orders_status s
    on o.orders_status = s.orders_status_id 
    left outer join
    (
        select 
            orders_id,
            sum(ifnull(paid_amount, 0)) as paid_total
        from
            orders_payment 
        where
            confirm_payment = '1'
        group by
            orders_id
    ) op 
    on
        op.orders_id = o.orders_id
where
    ot.class = 'ot_total' and 
    s.language_id = '1' and
    round(ot.value,2) != round(ifnull(op.paid_total, 0), 2);

I think this will give the optimizer a better chance to do a good job.

Notice that I have put a "group by" in the inner query for "op". Without that I think you may trick the optimiser into running this query for every result row rather than just once.

With the volumes you have you should not need any indexes; they would probably make things worse rather than better, but test it and see what happens.

I haven't been able to test my suggestion, but if you provide create table scripts and some data, I would do that. Apologies if I've made any typos in the query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download