Re: Up to 1 million rows Performance + Design Help
Posted by: Rick James
Date: May 18, 2009 09:34AM

It can be difficult to balance normalization with search performance.

Your example happens to be easy. genre="Mystery" -> genre_id=3. That is, that is a cheap JOIN. In the general case, the JOINs can be costly.

The principle behind FULLTEXT being the first index to use: It will usually be the most selective. This leads to having very few items to then check against other indexes. This, of course, can go wrong.

Even in one table, "Nested Loop Join" is usually used -- that is, the query might run like this:
FOREACH match('mystery')  -- using one index
    IF the desired genre  -- check the row (or reach into another table)
        deliver a row  -- (or count thru LIMIT)

The "merge index" (new with 5.0) will sometimes (don't know about your example) do the JOIN in a smarter way. The IF statement above might use a second index in the same table.

Unfortunately, you can't really have a 'compound index' that has both FULLTEXT and a regular INDEX component. That would be optimal for your example.

LIMIT 200, 20 -- this smacks of pagination, which has its own issues with performance, consistency, etc.

Options: ReplyQuote




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.