How to implement faster Search As You Type (SAYT) api in Rails 3 application

Question

I am having millions of records in my mysql database. I implemented a normal REST api for the iPhone app in Rails 3, but the SAYT feature response is really slow. It takes a lot of time to search the database and return the results. How can I improve the performance?

I have indexed my tables. What all aspects should I look more into like MySQL tuning, or should I use rails sphinx or sunspot? Will that help Please help me with all your expert advices.

Answer 1

I agree with the general answer: use a search engine like Sphinx (and limit the number of results returned); they are designed to do exactly what you want.

However, while millions of records may sound like a lot, you should first determine what is taking a long time. I have major love for Sphinx and ThinkingSphinx -- they take what is a rather complex process and make it pretty simple and easy. But, in the end, a search engine it's another system to manage, configure, learn and know. If you don't have to go there, it's easier not to, right?

It might be the query, it might be time spent returning the data (limit is your friend!). Or it might be that you're getting hundreds of requests per second, perhaps because the delay on auto-complete is too short -- if a lookup occurs at every character, fast typists or multiple users can easily overrun the server with queries that provide no utility for the user.

Watch the Rails logs and see what's really going on. If it's a simple query performance issue, doing a complicated full-text search, then, yeah, that's going to be slow and Sphinx is going to be worth the effort. Your database has an explain tool that, with some work, can help you understand what the database is doing to get the result. It's not uncommon that an index doesn't get used.

What about caching? Memcached is a fantastic tool. Or maybe even just your buffer size settings for the database can allow it to use more memory for caching.

Answer 2

I am not sure what you mean by adding a faster search but it is good to limit your search results to 100 as it touches on usability. Not many users will go through 100 records for their search.

In order to achieve such search, i suggest that you include the keyword table. Keyword table should consist of the record id and the keyword associated to it and how many times the keyword has been transacted in the database.

Thus it will help you determine the top hundred records and the most accurate search.

There are many algorithms search as Map Reduce too which runs concurrently. I don;t think your mobile device technology can handle map reduce.

Answer 3

I would also recommend using a full text search engine like Sphinx.

There is a good screencast about using Sphinx and rails with the thinking_sphinx gem:

Railscast thinking_sphinx gem

With that gem you can also affect the search result regarding importance by e.g. adding field weights:

Thinking Sphinx documentation

Since it is a mobile device i would as well keep the ammount of results sent to the mobile to a minmum as madi allready mentioned it.

Have fun