GX-Entwicklung

The orders overview page in the Gambio Admin is very slow when there are tens of thousands of orders.

These requests are slow because of missing indices:

SELECT DISTINCT orders.customers_status AS id, orders.customers_status_name AS fallback_name, customers_status.customers_status_name AS status_name
FROM orders
LEFT JOIN customers_status ON (customers_status.customers_status_id = orders.customers_status)
LEFT JOIN languages ON (languages.languages_id = customers_status.language_id)
WHERE languages.languages_id = 2;

SELECT DISTINCT count(orders_status_history.orders_status_id) as count, `orders_status_history`.`orders_status_id`, `orders_status_name` FROM `orders_status`, `orders_status_history` WHERE `language_id` = '2' AND `orders_status`.`orders_status_id` != 99 AND `orders_status_history`.`orders_status_id` = `orders_status`.`orders_status_id` GROUP BY `orders_status_id` ORDER BY `count` DESC;

SELECT DISTINCT `delivery_country_iso_code_2`, `delivery_country` FROM `orders`;

SELECT DISTINCT `shipping_class` FROM `orders`;

The solution:

ALTER TABLE `orders` ADD INDEX `customers_status_index` (`customers_status`, `customers_status_name`);
ALTER TABLE `orders` ADD INDEX `delivery_country_index` (`delivery_country_iso_code_2`, `delivery_country`);
ALTER TABLE `orders` ADD INDEX `shipping_class` (`shipping_class`);
ALTER TABLE `orders_status_history` ADD INDEX `orders_status_id` (`orders_status_id`);

For testing (review 2) just ensure that installer and updater create the index successfully.