GX-Entwicklung

GX-Bug #70464

Von Moritz Bunjes vor etwa 1 Jahr aktualisiert

The Ajax request to load the orders overview on the orders page in the Gambio Admin is very slow when there are tens of thousands of orders. 

 These requests are slow because of missing indices: The Method OrdersOverviewColumns::_getGroupOptions contains the SQL 
 ``` 
 SELECT DISTINCT orders.customers_status $rows = $this->db->distinct() 
             ->select('orders.customers_status AS id, orders.customers_status_name AS fallback_name, customers_status.customers_status_name ' 
                      . 'customers_status.customers_status_name AS status_name 
 FROM orders 
 LEFT JOIN customers_status ON (customers_status.customers_status_id status_name') 
             ->from('orders') 
             ->join('customers_status', 
                    'customers_status.customers_status_id = orders.customers_status) 
 LEFT JOIN languages ON (languages.languages_id orders.customers_status', 
                    'left') 
             ->join('languages', 'languages.languages_id = customers_status.language_id) customers_status.language_id', 'left') 
             ->where('languages.languages_id', 
                     $_SESSION['languages_id']) 
             ->get() 
             ->result_array(); 
 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; which is slow because of a missing index for the fields orders.customers_status and orders.customers_status_name. 

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

 SELECT DISTINCT `shipping_class` FROM `orders`; Solution: 
 ``` 

 The solution: 
 ``` 
 ALTER TABLE `orders` `feature`.`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.

Zurück