Re: Mysql sub query optimization

[JH NG]

DERIVED

id select_type 	table 	type 	possible_keys 	key 	 key_len 			ref 		rows 	Extra
1 PRIMARY 	c2s 	index 	PRIMARY 	PRIMARY  8 	NULL 			75 	Using where; Using index; Using
												temporary; Using filesort
1 PRIMARY 	c 	eq_ref 	PRIMARY 	PRIMARY  4 	c2s.category_id 	1 	Using where
1 PRIMARY 	<derived2> ALL 	NULL 	 	NULL 	 NULL 	NULL 			58 	
1 PRIMARY 	cd 	eq_ref 	PRIMARY 	PRIMARY  8 	c.category_id,const 	1 	Using where
2 DERIVED 	p2c 	index 	PRIMARY 	PRIMARY  8 	NULL 			2654 	Using index; Using temporary; Using 
												filesort
2 DERIVED 	p2s 	eq_ref 	PRIMARY 	PRIMARY  8 	p2c.product_id 		1 	Using where; Using index
2 DERIVED 	p 	eq_ref 	PRIMARY 	PRIMARY  4 	p2s.product_id 		1 	Using where

---------------------------------
DEPENDENT SUBQUERY

id select_type 		table 	type 	possible_keys 	key 	 key_len ref 				rows 	Extra
1 PRIMARY 		c2s 	index 	PRIMARY 	PRIMARY  8 	NULL 				75 	Using where; Using index;
														Using temporary; Using
														filesort
1 PRIMARY 		c 	eq_ref 	PRIMARY 	PRIMARY  4 	c2s.category_id 		1 	Using where
1 PRIMARY 		cd 	eq_ref 	PRIMARY 	PRIMARY  8 	c.category_id,const 		1 	Using where
2 DEPENDENT SUBQUERY 	p2s 	index 	PRIMARY 	PRIMARY  8 	NULL 				1111 	Using where; Using index
2 DEPENDENT SUBQUERY 	p 	eq_ref 	PRIMARY 	PRIMARY  4 	p2s.product_id 			1 	Using where
2 DEPENDENT SUBQUERY 	p2c 	eq_ref 	PRIMARY 	PRIMARY  8 	p.product_id,c.category_id 	1 	Using where;
														Using index