MySQL Forums
Forum List  »  Optimizer & Parser

Re: Distinct/group by, order by and filesort
Posted by: Rick James
Date: February 25, 2010 01:34PM

Seems like (`section_id`,`article_id`) would be unique in section_to_article -- maybe you could make it the PRIMARY KEY (and toss `id`). At that point, you may as well have the other index be (`article_id`,`section_id`). This will be slightly better for the query you pose -- it can be handled "Using index".

Recommend breaking `body` and maybe `keywords` and `headline` (and maybe some of the VARCHAR(255) fields) into a separate table ("vertical partitioning"). This will leave 'article' less bulky, thereby faster to search. The JOIN to get the body is not (I assume) always needed; when it is, it will be "fast enough".

Note: splitting out the FULLTEXT fields would limit the number of columns in MyISAM. FULLTEXT always takes precedence over other indexes, so this does not really change the EXPLAIN plan.

The ENUMs should also (probably) be NOT NULL.

Options: ReplyQuote

Written By
Re: Distinct/group by, order by and filesort
February 25, 2010 01:34PM

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.