What method should be used for searching this mysql dataset?

Question

I've got a mysql dataset that contains 86 million rows.

I need to have a relatively fast search through this data.

The data I'll be searching through is all strings.

I also need to do partial matches.

Now, if I have 'foobar' and search for '%oob%' I know it'll be really slow - it has to look at every row to see if there is a match.

What methods can be used to speed queries like this up?

G-Man

Answer 1

What you seek is Full Text Search. This would enable you to do partial matches quickly and searches on multiple columns quickly.

Answer 2

I don't think fulltext search will allow for partial matches (I could be wrong on this).

You might take a look at Sphinx Search which is a full-text and partial match search engine system. You can easily import your mysql data into it, and then use simple PHP queries to search the data. It is far more efficient than using MySQL to do the query.