On the admin side, the orders page takes a long time to load..

[esplindia1]

On the admin side, the orders page takes a long time to load..

 

I have more than 200,000 orders (which is not really a lot when compared to an ecommerce site)

 

After investigating the issue it was found that the following query runs very slow..

 

select count(*) as total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total'

 

This query is generated using the splitpageresults()

 

Is there any way to reduce execution time of this query?

[jigga1234]

200,000????

 

are you sure do you mean 200,000 new orders or 200,000 in total?

[♥ecartz]

About the simplest change would be to replace

    } else {
     $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
   }

with

    } else {
     $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where (o.date_purchased > DATE_SUB(NOW(), INTERVAL 1 WEEK) or o.last_modified > DATE_SUB(NOW(), INTERVAL 1 WEEK)) and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
   }

in admin/orders.php

 

That would change things so that it would only show the most recent week's orders (change 1 WEEK to something else if you prefer) rather than all the orders. You might then need to add an index on the o.date_purchased and o.last_modified columns to get things to be performant.

 

A more complicated change would be to replace split_page_results with custom logic just for the orders page that allowed you to set a count query. For example, the following query would be fast and should give the same results:

select count(orders_id) from orders

[esplindia1]

200,000????

 

are you sure do you mean 200,000 new orders or 200,000 in total?

 

 

200,000 in total orders....

[esplindia1]

About the simplest change would be to replace

    } else {
     $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
   }

with

    } else {
     $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where (o.date_purchased > DATE_SUB(NOW(), INTERVAL 1 WEEK) or o.last_modified > DATE_SUB(NOW(), INTERVAL 1 WEEK)) and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
   }

in admin/orders.php

 

That would change things so that it would only show the most recent week's orders (change 1 WEEK to something else if you prefer) rather than all the orders. You might then need to add an index on the o.date_purchased and o.last_modified columns to get things to be performant.

 

A more complicated change would be to replace split_page_results with custom logic just for the orders page that allowed you to set a count query. For example, the following query would be fast and should give the same results:

select count(orders_id) from orders

 

 

Thanks,

 

I had tried with select count(orders_id) from orders but the execution still not fast....