MySQL query eating my CPU

[krnl]

Do you see anything wrong with this query? It just started acting up today, so I am wondering if either the query got mangled or maybe I have a database table that's not happy. I'm fairly certain that this query has been working fine for months...until today. Now it takes about 30 seconds to finish.

 

tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, m.manufacturers_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and p.manufacturers_id = m.manufacturers_id order by s.products_id desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

The actual query (with the tablenames filled in and all) is as follows:

select distinct p.products_id, p.products_image, p.products_tax_class_id, m.manufacturers_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from manufacturers m, products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '84' and p.products_status = '1' and p.manufacturers_id = m.manufacturers_id order by s.products_id desc limit 21

[♥FWR Media]

Do you see anything wrong with this query? It just started acting up today, so I am wondering if either the query got mangled or maybe I have a database table that's not happy. I'm fairly certain that this query has been working fine for months...until today. Now it takes about 30 seconds to finish.

 

tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, m.manufacturers_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and p.manufacturers_id = m.manufacturers_id order by s.products_id desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

The actual query (with the tablenames filled in and all) is as follows:

select distinct p.products_id, p.products_image, p.products_tax_class_id, m.manufacturers_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from manufacturers m, products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '84' and p.products_status = '1' and p.manufacturers_id = m.manufacturers_id order by s.products_id desc limit 21

 

 

Got to phpmyadmin

 

Choose the correct database

 

Choose SQL at the top

 

In the textbox put ..

 

alter table banners add index idx_banners_group (banners_group); alter table banners_history add index idx_banners_history_banners_id (banners_id); alter table currencies add index idx_currencies_code (code); alter table customers add index idx_customers_email_address (customers_email_address); alter table customers_basket add index idx_customers_basket_customers_id (customers_id); alter table customers_basket_attributes add index idx_customers_basket_att_customers_id (customers_id); alter table orders add index idx_orders_customers_id (customers_id); alter table orders_products add index idx_orders_products_orders_id (orders_id); alter table orders_products add index idx_orders_products_products_id (products_id); alter table orders_status_history add index idx_orders_status_history_orders_id (orders_id); alter table orders_products_attributes add index idx_orders_products_att_orders_id (orders_id); alter table orders_products_download add index idx_orders_products_download_orders_id (orders_id); alter table products add index idx_products_model (products_model); alter table products_attributes add index idx_products_attributes_products_id (products_id); alter table reviews add index idx_reviews_products_id (products_id); alter table reviews add index idx_reviews_customers_id (customers_id); alter table specials add index idx_specials_products_id (products_id); alter table zones add index idx_zones_to_geo_zones_country_id (zone_country_id); alter table orders_status add public_flag int DEFAULT '1'; alter table orders_status add downloads_flag int DEFAULT '0'; alter table orders modify payment_method varchar(255) NOT NULL; alter table whos_online modify last_page_url text NOT NULL;

 

Press go!

 

Try the query again and report back (indexes as recommended RC2a)

[krnl]

That did seem to help a bit. Still not as quick as I would like it to be. It still takes 5 seconds to run the query from the mysql command line. I'm fairly certain that it was faster than that yesterday....prior to the table indexes being added.

 

Thanks for the RC2a table index info.

[♥FWR Media]

That did seem to help a bit. Still not as quick as I would like it to be. It still takes 5 seconds to run the query from the mysql command line. I'm fairly certain that it was faster than that yesterday....prior to the table indexes being added.

 

Thanks for the RC2a table index info.

 

How long has the site been up? how many users visitors etc?

[krnl]

Site's been up for 3+ years. The products table has about 20,000 entries. Anywhere from 3-10 visitors online at any given time.

[♥FWR Media]

Site's been up for 3+ years. The products table has about 20,000 entries. Anywhere from 3-10 visitors online at any given time.

 

Have you ever run an analyze or optimize routine?

[krnl]

I ran some optimizations that were scattered throughout the boards for standard OSC queries. This one isn't standard though. I went through and found that it was the products table (of course) that was causing the problem. Added a new index to that table and selected by that index to cut the query time down. Looks great now.

 

Thanks for the ideas and pointers gurglebaby :)

[Envelo]

Hello, how you fix it? because i have the same problem

 

 

Thank you