Originally Posted by
bod
Hi Guys, we just finished an upgrade from 1.26d to 1.3.7 and things are running very slowly, and causing problems because of it. It appears to be an issue with our mySQL database performance.
...
For example, the query below takes usually about 7 seconds on the new schema, and is almost immediate on the old schema. And it's in the slow query file as having taken up to 33 secs to.
"select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address from <your_prefix>_orders o left join <your_prefix>_orders_total ot on (o.orders_id = ot.orders_id), <your_prefix>_orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by o.orders_id DESC limit 0, 20;"
There are two solutions to your problem:
1. On the orders_total table, drop the index that's been added for the "class" field.
or
2. Alter the query to make the join more efficient:
select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address from TABLE_PREFIX_orders o left join TABLE_PREFIX_orders_total ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total'), TABLE_PREFIX_orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' order by o.orders_id DESC limit 0, 20
If you do an EXPLAIN after making *either* of those changes, you'll find that the performance is good again.
FYI - similar issue discussed here, and has been incorporated into the v1.4 release: http://www.zen-cart.com/forum/showthread.php?t=79408
Bookmarks