I'm working on a really elaborate osCommerce project. There's one query that's bogging down pretty badly and indexing does not help one bit. I'm wondering if the query is CPU-bound or perhaps I/O bound. It looks like this:
Code:
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 ot.class = 'ot_total';
I've tried adding a variety of indexes:
* The orders_id fields are indexed in both tables involved in the left join.
* The orders_status field is indexed in the orders table.
* the orders_status_id field is indexed in orders_total
* the class field is indexed in the orders_total table.
The problem appears to be that indexing the status_id and class fields doesn't help at all. With a maximum of 3-6 distinct values in those fields, indexing provides no performance gain and I end up with a table scan.
Explaining the query yields the following:
Code:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ot ref idx_orders_total_orders_id,class class 34 const 359765 Using where
1 SIMPLE o eq_ref PRIMARY,orders_status PRIMARY 4 newmig2.ot.orders_id 1
1 SIMPLE s eq_ref PRIMARY PRIMARY 4 newmig2.o.orders_status 1 Using index
Any thoughts on how to make this faster? I find myself wondering if the query is CPU- or I/O bound. Is there any hope here?
More information: The orders table has about 300,000 records in it. The orders_total table has about 600,000 records in it.
orders.MYD is 74MB
orders.MYI is 8.9MB
orders_total.MYD is 30MB
orders_total.MYI is 15MB
