Searching for users in the database, how more efficient?

Question

PHP

Good day. There is a table with ~ 5000 users, you need to optimize the search for these records: the user enters a few characters and the list of matches is dynamically unloaded from the database. The closest example is Habr's Dialogs, the "Enter username" field.

Tools: MySQL, PHP.

Two solutions came to mind:

1) AJAX to send by keyup (for example) the value of the field and make a LIKE %% request. As for me, this will strain MySQL a lot;

2) Create a static variable, into which all records are unloaded the first time, and then look for matches using PHP. For PHP it will not be difficult to work with such an array (I repeat ~ 5000 entries + additional fields, such as id, etc.)?

Can you tell me how to do it better? Perhaps I wrote crazy solutions above.

Thanks in advance.

Answer 1

Can you hold the contents of a php variable for as long as the user enters data? That is, your PHP request can hang in memory indefinitely? This rarely happens.
I would cache in Redis and drag it from there. There is long-polling - by long-polling, no - by key-up. When adding a user, update the cache.

Answer 2

5000 records is not a lot, a simple ajax request and LIKE %% should be enough.

Answer 3

Why not try Sphinx ?
Set it up once and be there at least 500k users, sphinx will deal with it pretty quickly.

Answer 4

We cache the data in the form id:name to a file on disk. When requested, load the file and do a search. Zero queries to the database. The cache is updated when a new user is added or a user is updated.

Answer 5

So there are ready-made solutions. We've tried, it works on a base of just a few thousand items ( hardworm will correct me if I'm wrong). We used Ajax Autocomplete for jQuery .
As for the query to a table of ~ 5000 items, it doesn’t work for any database, provided that there is an index on the corresponding field.

Answer 6

And you can also ajax + sphinx and full-text search and the database almost does not load, but this is already a radical solution :-)

Answer 7

Premature optimization is the root of all evil. © Knut
The usual request is enough. If your load grows to such a size that the database will fall from the select, then caching is already turned on.