Caching data in a Rails variable instead of querying the database?

Question

I have a list of about 15000 items in my database, every one of them has a unique ID and name. New items are added regularly but usually not more than ten a day.

My frontend uses a form with autocompletion and this autocompletion is based on the name of the items above. That means that every time someone hits a key, an AJAX request is sent to the API of my application to get an updated list of items as response (JSON). 15000 is not a big number but I see the performance impact this feature has on the application due to heavy use of this form.

I currently use something like this to find what my users are looking for and its good enough most of the time: "... LIKE %search_query%" (simplified). There are some cases however, where some kind of fuzzy search would probably be beneficial.

My question is: Would it be a good idea to keep all items in an array of hashes instead of querying the database every time? And if so, where would I keep such a variable in a Rails application so that I can access it whenever I need to?

Answer 1

SQL is pretty solid and easy to use so I will try to find ways to improve the performance of the SQL search first. Try google stuff like: sql fast text search https://stackoverflow.com/questions/830241/sql-server-index-on-text-column

• gnat: this reads more like a comment, see How to Answer
• Jan Doggen: This does not answer the question