Quick search optimization by keydown

Question

MySQL

You need to add a quick search by type to the site, like VKontakte.



As you type in the word, search results will appear in the div below the input field. The search will be based on news (about 15-20 thousand titles), each news also has a small logo. The title will be a link to the news itself.

But every time you enter a letter, making a query to the database is painfully “expensive”. How can this be optimized? Of the options so far:

1. Make 1 request to extract all the names into a js variable and then search locally, using js. But it will be necessary to pull all 20k names every time.
2. Can somehow drive a cache with all the names, logos into the server's memory and take it from there with pom. ajax?

Answer 1

If you have a search only for the first part of the news title, then create a memory table in the database with an index by title and enter the title of the news and the logo into it. Databases are kind of made to be searched; searching an indexed in-memory table is not a very expensive pleasure.

Answer 2

there is no need to pull the database for each letter. make a delay, if there is no input within 200-400ms (choose it yourself as it will be more convenient), then start the search.
In this case, the number of requests will be significantly reduced. and there is no point in actually searching while the user is typing. as soon as he made a short pause in the set, you can display hints for him.

Answer 3

Use the cache, and the 1st tip. There is simply no other way. I also recommend making the search start with more than 2-3 characters in order to reduce the number of results. And don't forget the limit.

Answer 4

With each press, first cancel the previous request, a person, when looking for something specific, enters letters faster than he fulfills the request.

Answer 5

I would make a pause of at least 500 milliseconds and a limit of 10 entries. So the load on the database will be less.

Answer 6

Do a search on the sphinx.