Slow queries

[lyndsiesal]

All of a sudden my logs are filled with these slow queries and I have no idea what caused it or how to fix it. I optimized my tables. I'm not experienced enough to know what else to do. I would really, really appreciate any advice. It looks like this, over and over, but with different product ids.

 

# Query_time: 69 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o, products p where opa.products_id = '266' and opa.orders_id = opb.orders_id and opb.products_id != '266' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit 6

[steve_s]

All of a sudden my logs are filled with these slow queries and I have no idea what caused it or how to fix it. I optimized my tables. I'm not experienced enough to know what else to do. I would really, really appreciate any advice. It looks like this, over and over, but with different product ids.

 

# Query_time: 69 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o, products p where opa.products_id = '266' and opa.orders_id = opb.orders_id and opb.products_id != '266' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit 6

 

Run this sql on your store's database it will add indexes and speed it up

ALTER TABLE `products_to_categories` DROP PRIMARY KEY;
ALTER TABLE `products_to_categories` ADD INDEX `idx_p2c_categories_id` ( `categories_id` );
ALTER TABLE `products_to_categories` ADD INDEX `idx_p2c_products_id` ( `products_id` );
ALTER TABLE `categories` ADD INDEX (`sort_order`);
ALTER TABLE `customers` ADD INDEX (`customers_email_address`);
ALTER TABLE `orders` ADD INDEX (`customers_id`);
ALTER TABLE `products` ADD INDEX (`products_model`), ADD INDEX (`products_price`), ADD
INDEX (`products_date_available`), ADD INDEX (`manufacturers_id`);
ALTER TABLE `products_attributes` ADD INDEX (`products_id`), ADD INDEX (`options_id`);
ALTER TABLE `products_options` ADD INDEX (`products_options_name`);
create index IDXSPECIALSPRICE on specials (products_id,status,specials_new_products_price);