Laravel Jquery Auto-complete Speed

I am using Laravel. The autocomplete speed is slow. I know that it might be due to the fact that I have a lot of information my database. I have a total of 38432 rows in the table.

Solution 1:

"Slow" is a relative term. I would recommend measuring your select statement speed to give readers an idea of what you mean, and perhaps also what sort of speed improvements you are looking for. In PHP, you can do this with microtime(true) to get a floating-point representation of the time.

Your hardware is probably the first place to start. Are you running this on a development machine or shared PHP hosting or a dedicated VPS with plenty of RAM? This stuff matters. If the problem is exhibited in live, then maybe your database server is not up to snuff.

Indexes have been recommended to you. They are a database feature that make data faster to search for, at a cost of insert speed, and they are often a good idea. However, on MySQL, indexes are not used for LIKE %x% queries - the preceding wildcard means they cannot be used. A "full table scan" is required, which is what happens when you do not have an index (this is why you found they made no difference).

Speed-up strategy

If you really cannot upgrade your database server, you could create a new table that joins to your table that splits up all of your words, so you can do a match on one side only.

For example, consider this address part:

Greenwich, London

I assume from your query that you want your autocomplete to match when the user starts typing either of these:

Gre
Lon

However, it is not terrible if you do not get matches with these:

wich
don

Therefore, from your table, join to another table called words, so the above entry gets two related entries in the words table:

Greenwich
London

You would need to create a process to keep the words table in sync with the cities table. Then, you can do this:

SELECT*FROM address
INNERJOIN word ON (words.address_id = address.id)
WHERE word.word LIKE'?%'

The ? would then be replaced with the user's current word, which can be done in JavaScript (splitting on space). The bonus then is that you've found a match, and you can either suggest the word or the whole address phrase, as you prefer. And the good news is that MySQL can use an index again, since you've avoided the percent on the left-hand side!

Experiment with running queries directly

To see if an index makes any difference, try entering queries into your database client using the EXPLAIN syntax. For example, try the above query:

EXPLAIN
SELECT*FROM address
INNERJOIN word ON (words.address_id = address.id)
WHERE word.word LIKE'?%'

You've found out how to use CREATE INDEX (you can run that directly in your database client, you don't have to do it in PHP), and you can use DROP INDEX as well. These will help you discover the difference (if any) when running specific queries.