Most efficient way to search a database with more than a billion records?

Question

My client has a huge database containing just three fields:

1. Primary key (a unsigned number)
   
2. Name (multi-word text)
   
3. Description (up to 1000 varchar)
   

This database has got over few billion entries. I have no previous experience in handling such large amounts of data.

He wants me to design an interface using AJAX (like Google) to search this database. My queries are as slow as turtle.

What is best way to search text fields in such a large database? If the user is typing wrong spelling on interface, how can I return what he wanted ?

Answer 1

If you are using FULLTEXT indexes, you're correctly writing your queries, and the speed in which the results are returned are not adequate, you are entering a territory where MySQL may simply not be sufficient for you..

You may be able to tweak settings, purchase enough RAM to make sure that your entire data-set fits 100% in memory. It's definitely true that performance gains could be huge there.

I'd definitely recommend looking into tweaks of your mysql configuration. We've had some silly settings in the past. Operating system defaults tend to really suck!

However, if you have trouble at that point, you can:

1. Create a separate table containing each word (indexed) along with a record id that it refers to. This will allow you to search on single words.
2. Use a different system that's optimized for solving this problem. Unless my information is now outdated, the 2 engines that are the most popular for solving this problem are:
   1. Sphinx
   2. Solr / Lucene