Re: best design for text searches on 2M+ rows with preference on recent entries
Posted by: Rick James
Date: July 26, 2011 10:55PM

> This presents a problem because the most recent entries will naturally be at the very end of the database, and it seems that searching with large offsets (or reverse order) takes more time.
No. MySQL can (usually) search backwards just as fast as forward. Offsets, per se, are not an issue.

FULLTEXT is much faster than LIKE '%word%'.

Check out Lucene and Sphinx a alternatives to FULLTEXT.

For your query:
SELECT  *
    FROM  table
    WHERE  title LIKE '%term%'
      OR  content LIKE '%term%'
      OR  categories LIKE '%term%'
      OR  tags LIKE '%term%'
    ORDER BY  date DESC
    LIMIT  0 , 30
converted to FULLTEXT:
SELECT  *
    FROM  table
    WHERE  MATCH ('+term') AGAINST (title, content, categories, tags IN BOOLEAN MODE)
    ORDER BY  date DESC
    LIMIT  0 , 30
You wild need a single FULLTEXT index with all the text fields:
FULLTEXT (title, content, categories, tags)

Vertical partitioning (splitting off a second table) would not benefit you much -- in this case.

> "blue widgets"
WHERE MATCH ('+blue +widgets') AGAINST (title, content, categories, tags IN BOOLEAN MODE)

> proximity search
Hot off the press! http://forums.mysql.com/read.php?107,428270,428270

If you chase your design, you will get quagmired in designing, implementing, improving, debugging, etc, it. Just use FULLTEXT, and live with its limitations. "Time to market" is important for most of us.

Options: ReplyQuote


Subject
Written By
Posted
Re: best design for text searches on 2M+ rows with preference on recent entries
July 26, 2011 10:55PM


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.