Laravel jQuery auto-complete speed

Question

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. But I'm sure Facebook, Twitter etc are have more than I do.

How can I speed up up the autocomplete?

Here is my code:

class AutoCompleteController extends Controller {

  public function index() {
    return view('layouts.mess');
  }

  public function aa(Request $request){
    $term = $request->get('term');

    //$queries=Profile::distinct('cityandstate','LIKE','%'.$term.'%')->take(5)->get();
    $queries = all::where('cityandstate', 'like',  $term . '%')->offset(5)->limit(15)->orderBy('cityandstate','dec')->get();

    foreach ($queries as $query) {
      $results[] = ['value' => $query->cityandstate ];
    }       

    return response()->json($results);
  }
}

JavaScript:

<script type="text/javascript">
    $( function() {
      $( "#location-input" ).autocomplete({
        //html : html,
        source: "display-searches",
        minLength: 1,
        select:function(e,ui) { 
          $("#location-input").val(ui.item.label);
        }
      });
    });




</script>

Could add indexes to the columns you do WHERE's on. Can also cache query results. laravel has built in caching easy peasy. — Andrew, Feb 21 '18 at 19:01
@Andrew thanks for replying. I'm embarrassed cause I have been coding for years and I never heard of adding indexes. What is that exactly? — , Feb 21 '18 at 19:15
Well it speeds up queries that need to where or join on that column. Works on text as well as long as you LIKE only on the right side and not the start of the text (which is what you are doing.). https://stackoverflow.com/questions/2889827/indexing-a-mysql-text-column — Andrew, Feb 21 '18 at 19:32
u can use mysql workbench or php my admin etc to add them as well — Andrew, Feb 21 '18 at 19:33
Never mind I did it I typed in $connect->query('CREATE Index Ix_cityandstate ON alls(cityandstate)'); if($connect){ echo"Created"; }else{ echo"Not created"; } — , Feb 21 '18 at 19:53
if the index is created, then its created, what would you like to do next? — Andrew, Feb 21 '18 at 19:56
mysql index has nothing to do with php / laravel i dont think — Andrew, Feb 21 '18 at 20:00
its not fast. What am I supposed to do if its has nothing do do with it. I am using laravel — , Feb 21 '18 at 20:03
I think, your query is fubar, you are using a 'like' operator, but then also adding ".%" to the end ? Double like might be bad. — Andrew, Feb 21 '18 at 20:07
what's this all:: ? Does that search every table? that could be slow. Why not query just your table. — Andrew, Feb 21 '18 at 20:26
try re-working your query https://stackoverflow.com/questions/29276065/order-by-row-and-limit-result-in-laravel-5 — Andrew, Feb 22 '18 at 18:49
try outputting your query into raw sql, https://stackoverflow.com/questions/18236294/how-do-i-get-the-query-builder-to-output-its-raw-sql-query-as-a-string then test the straight query in your mysql workbench, find out if the query/ a line in the query is slow, or is it actually laravel — Andrew, Feb 22 '18 at 19:03
how and where do i add it? I had did something like this in my javascript code flushCache(); — , Feb 22 '18 at 19:09

halfer · Answer 1 · 2018-02-23T11:18:24.383

"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
INNER JOIN 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
INNER JOIN 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.

Your explanation is great but unlike the example above im dealing with services as opposed to address. All i want to do it to search for a service - Painter etc. Will it still be necessary to have a seperate words table? I am using a right side % search - e.g 'Pai%' will suggest Painter. My table is indexed. Yet the speeds are still not that fast — Screwtape007, Jun 09 '19 at 07:06
@Screwtape007: by coincidence, I just wrote an auto-completer. One thing to watch out for is, if you do not have a words table, whether you need to do matches on separate words in your table. For example, if you have the table phrase "Painters and Decorators" and your user does a search for "Decorators", then you would need to do `phrase LIKE 'Decorators% OR phrase LIKE '% Decorators%', in order to catch instances of "Decorators" that are not first words. This turns searches into full table scans though, which are quite slow. — halfer, Jun 09 '19 at 08:41
However, in my case, I was matching on two things: words (for which we had a words table) and types (for which we would have no more than ~300, so the `LIKE` trick about was fine). We're also running on AWS Aurora PostgreSQL instances, which are extremely fast. — halfer, Jun 09 '19 at 08:43
If you are unhappy with your speed then you could ask a new question with some details of what database you are using in staging/production, your table layout and column types, your database type (MySQL/PostgreSQL/etc), what indexes you have, how many rows your tables have and what sort of speeds you are getting. It may be that you need a words table. — halfer, Jun 09 '19 at 08:46
In fact in my case I went one better and created an indexer that stored all word combinations from the left-hand side: (`p` -> `painter`, `pa` -> `painter`, `pai` -> `painter`, `pain` -> `painter`, etc). This changes all search operations to trivial equality comparisons, which are extremely fast for relational databases. — halfer, Jun 09 '19 at 08:47

Laravel jQuery auto-complete speed

1 Answers1

Speed-up strategy

Experiment with running queries directly

Linked