Admin Dashboard Orders Module and Admin Orders Listing Load Times

[frankludriks2]

I have a lot of orders. Using out of the box, load time for the admin dashboard orders module for my site is around 13 seconds, load time for listing of orders (admin/orders.php) is around 8 seconds.

Obviously way too long.

I propose a new column in ORDERS table, order_total. Order total is added to this column when inserting order (insert_order.php).

Adding the order_total column (and corresponding value for each order), then changing the query in admin/includes/modules/dashboard/d_orders.php from

$orders_query = $GLOBALS['db']->query(sprintf(<<<'EOSQL' SELECT o.orders_id, o.customers_name, COALESCE(o.last_modified, o.date_purchased) AS date_last_modified, s.orders_status_name, ot.text AS order_total FROM orders o INNER JOIN orders_total ot ON o.orders_id = ot.orders_id INNER JOIN orders_status s ON o.orders_status = s.orders_status_id AND s.language_id = %d WHERE ot.class = 'ot_total' ORDER BY date_last_modified DESC LIMIT %d EOSQL , (int)$_SESSION['languages_id'], (int)MODULE_ADMIN_DASHBOARD_ORDERS_DISPLAY));

to

$orders_query = $GLOBALS['db']->query(sprintf(<<<'EOSQL' SELECT o.orders_id, o.customers_name, o.order_total, COALESCE(o.last_modified, o.date_purchased) AS date_last_modified, s.orders_status_name FROM orders o INNER JOIN orders_status s ON o.orders_status = s.orders_status_id AND s.language_id = %d ORDER BY date_last_modified DESC LIMIT %d EOSQL , (int)$_SESSION['languages_id'], (int)MODULE_ADMIN_DASHBOARD_ORDERS_DISPLAY));

cuts load time down to 2 seconds, saving 11 seconds.

Changing query in admin//includes/actions/orders/views/default.php from

$orders_sql = sprintf(<<<'EOSQL' SELECT o.*, s.orders_status_name, ot.text AS order_total FROM orders o INNER JOIN orders_total ot ON o.orders_id = ot.orders_id LEFT JOIN orders_status s ON o.orders_status = s.orders_status_id AND s.language_id = %d WHERE ot.class = 'ot_total' EOSQL , (int)$_SESSION['languages_id']);

to

$orders_sql = sprintf(<<<'EOSQL' SELECT o.*, s.orders_status_name FROM orders o LEFT JOIN orders_status s ON o.orders_status = s.orders_status_id AND s.language_id = %d EOSQL , (int)$_SESSION['languages_id']);

cuts load time down to .4 seconds, saving 7.6 seconds.

Thoughts?

[gburton]

I don't see any downside to this.

[ecartz]

There's a bit of a data normalization issue on having the order total in two places. If we would do this, we should probably remove the order total from the order_totals table.

I also rather suspect that there are less impactful options here. For example, moving class out of the orders_total table and into its own table. Then we can put a numeric index on order_id, order_total_class_id and query by the numeric ID.

I'm actually somewhat surprised that this is an issue at all. It suggests a serious failure in the query optimizer. Perhaps an index on the date column and a removal of the COALESCE (because modifying an order shouldn't refresh the time).