MySQL Forums
Forum List  »  Optimizer & Parser

table index optimization
Posted by: Matus Zeman
Date: May 01, 2012 10:55AM

Hi there,
I've spend some time by trying to optimize this sql query below. I could not find better way though. Do you have any ideas how this could be optimized further?
Thanks for any help!

Matus

Query:
SELECT  `articles`.`id` ,  `articles`.`R_headline` AS title,  `articles`.`primary_category` AS channel
FROM  `A_articles` AS  `articles` 
WHERE (
articles.`valid_from` < NOW( )
)
AND (
articles.`valid_to` > NOW( )
)
AND (
`articles`.`on_listings` =  'yes'
)
AND (
`articles`.`parent_article` =0
)
AND  `articles`.`primary_category` =311
AND ( 1 = IF(  `articles`.`available` =  'no', _fn_site_preview_mode(
), 1 ) ) 
AND (
articles.subsection_select =68
)
AND index_id > 230941
ORDER BY articles.`index_id` ASC , articles.`article_display_date` ASC 
LIMIT 1

Explain:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	articles	index_merge	valid_to,index_id,parent_article,primary_category,valid_from,subsection_select	subsection_select,parent_article,primary_category	4,4,4	NULL	4942	Using intersect(subsection_select,parent_article,primary_category); Using where; Using filesort

A_articles table indexes:
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
A_articles	0	PRIMARY	1	id	A	32712	NULL	NULL		BTREE	
A_articles	1	R_headline	1	R_headline	A	32712	NULL	NULL		BTREE	
A_articles	1	valid_to	1	valid_to	A	6542	NULL	NULL		BTREE	
A_articles	1	active	1	active	A	1	NULL	NULL		BTREE	
A_articles	1	article_type	1	article_type	A	1	NULL	NULL		BTREE	
A_articles	1	available	1	available	A	1	NULL	NULL		BTREE	
A_articles	1	root_id	1	root_id	A	1	NULL	NULL		BTREE	
A_articles	1	root_country	1	root_country	A	1	NULL	NULL		BTREE	
A_articles	1	index_id	1	index_id	A	32712	NULL	NULL		BTREE	
A_articles	1	parent_article	1	parent_article	A	1	NULL	NULL		BTREE	
A_articles	1	roundup_article	1	roundup_article	A	1	NULL	NULL		BTREE	
A_articles	1	source_magazine	1	source_magazine	A	1	NULL	NULL		BTREE	
A_articles	1	article_display_date	1	article_display_date	A	32712	NULL	NULL		BTREE	
A_articles	1	primary_category	1	primary_category	A	111	NULL	NULL		BTREE	
A_articles	1	valid_from	1	valid_from	A	16356	NULL	NULL		BTREE	
A_articles	1	subsection_select	1	subsection_select	A	1	NULL	NULL		BTREE

Options: ReplyQuote


Subject
Views
Written By
Posted
table index optimization
2383
May 01, 2012 10:55AM
1161
May 02, 2012 07:08PM
1484
May 04, 2012 08:20AM
1355
May 05, 2012 09:07AM
1187
May 17, 2012 09:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.