There are several relevant tips in
http://mysql.rjweb.org/doc.php/ricksrots
Probably the most relevant is
Quote
* Having no compound indexes is a clue that you do not understand their power. INDEX(a,b) may be much better than INDEX(a), INDEX(b)
Indexing is very important to any database. Getting the "right" index can make a query run orders of magnitude faster. So, how to do that? Often "compound indexes" (multiple columns in a single INDEX(...)) are better than single-column indexes. A WHERE clause that has column=constant begs for an index that starts with that column. If the WHERE clause has multiple fields AND'd together, "="s should come first.
(also...)
Flags, and other fields with few values, should not be alone in an index -- the index won't be used.
In your case, here prettied up some:
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
One of these might be the best to add:
INDEX(primary_category, subsection_select, valid_from)
INDEX(primary_category, subsection_select, index_id)
(Add both of them; see which one the EXPLAIN picks.)