Clever Geek Handbook

Fast string binding to MySQL query

I have a MySQL database (InnoDB) with a table users

with a column username

. On the Ruby on Rails website, I have a feature that allows users to search for someone by username. When typing on the website, it prompts who the user is looking for (typeahead w / bootstrap). I am currently using a query in MySQL,SELECT `username` FROM users WHERE `username` LIKE 'the_query%' LIMIT 15;

The problem that exists is speed. The table has approximately 500,000 rows, and such a query seems to take about a second, which is too slow when you make guesses on the fly. What can I do to improve performance? Anything less than 100ms would be appropriate. Perhaps there is a better way to do this than using MySQL to handle the search?

+3


source to share


5 answers


Ended up adding an index FULLTEXT

as Keshan mentioned to the username column (previously only had a unique index) which seems to be 10x speed. The request takes 90 to 120 ms.



0


source


  • Create an index on the users table for fast results (it really does help to get results faster).
  • Make sure the user gets the results after typing some character (not just a single character search).


0


source


If you're looking exclusively from the beginning of a string username

, substring matching may be faster:SELECT `username` FROM users WHERE SUBSTRING(`username`, 0, CHAR_LENGTH('the_query'))='the_query';

0


source


Try to limit the number of matches you get. If it is a search bar, you cannot display thousands of entries in the search list. But from the above query, the server is sending all the results to the frontend as I'm guessing. And then you can filter the entries from that. Also, try this query.

Limit the number of entries to 10 ...

SELECT username

FROM users WHERE username

LIKE 'the_query%' LIMIT 10;

0


source


It will be faster if you create an md5 hash from username and password. Since the length is always 32 characters for each line, you can set an index over that field and use a very fast search on the indexed field.

0


source






More articles:


All Articles