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