MySQL Forums
Forum List  »  Optimizer & Parser

Re: table index optimization
Posted by: Rick James
Date: May 05, 2012 09:07AM

There are several relevant tips in
Probably the most relevant is


* 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.

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.)

Options: ReplyQuote

Written By
May 01, 2012 10:55AM
May 02, 2012 07:08PM
May 04, 2012 08:20AM
Re: table index optimization
May 05, 2012 09:07AM
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.