Slow MySQL Queries

[Nick Weisser]

Hi there,

 

Although all tables involved in this query seem to use indexes for the

id fields, they sometimes take up to 6 seconds:

 

# Time: 071204  0:17:17
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 59811
SELECT
			p.products_id as v_products_id,
			p.products_model as v_products_model,
			p.products_image as v_products_image,
			p.products_price as v_products_price,
			p.products_weight as v_products_weight,
			p.products_date_added as v_date_avail,
			p.products_tax_class_id as v_tax_class_id,
			p.products_quantity as v_products_quantity,
			p.manufacturers_id as v_manufacturers_id,
			subc.categories_id as v_categories_id
			FROM
			products as p,
			categories as subc,
			products_to_categories as ptoc
			WHERE
			p.products_id = ptoc.products_id AND
			p.products_model = '2702052' AND
			ptoc.categories_id = subc.categories_id;

 

Any pointers as to how it is possible to make this queries quicker would be very much appreciated. Do I need to create any additional indexes?

 

The store in question has 28000 products and almost 1000 categories, so maybe there's not much that can be done about it, but if there is, please let me know.

 

Nick

[♥FWR Media]

Hi there,

 

Although all tables involved in this query seem to use indexes for the

id fields, they sometimes take up to 6 seconds:

 

# Time: 071204  0:17:17
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 59811
SELECT
			p.products_id as v_products_id,
			p.products_model as v_products_model,
			p.products_image as v_products_image,
			p.products_price as v_products_price,
			p.products_weight as v_products_weight,
			p.products_date_added as v_date_avail,
			p.products_tax_class_id as v_tax_class_id,
			p.products_quantity as v_products_quantity,
			p.manufacturers_id as v_manufacturers_id,
			subc.categories_id as v_categories_id
			FROM
			products as p,
			categories as subc,
			products_to_categories as ptoc
			WHERE
			p.products_id = ptoc.products_id AND
			p.products_model = '2702052' AND
			ptoc.categories_id = subc.categories_id;

 

Any pointers as to how it is possible to make this queries quicker would be very much appreciated. Do I need to create any additional indexes?

 

The store in question has 28000 products and almost 1000 categories, so maybe there's not much that can be done about it, but if there is, please let me know.

 

Nick

 

 

Have you tried EXPLAIN?

 

If so what does it say?

[Nick Weisser]

I didn't even know that EXPLAIN exists :-" Thanks for the pointer!

 

Already found the solution to the problem, though:

ALTER TABLE `products` ADD INDEX `idx_products_model` ( `products_model` );

 

... reading the MySQL manual of the EXPLAIN statement it's pretty obvious that this is the perfect tool to exactly do what I needed to do

 

With the help of EXPLAIN, you can see where you should add indexes to tables to get a faster SELECT that uses indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order.