Search code examples
mysqlsqlquery-optimizationrdbms

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

Solution

  • 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.