Searching through database and the speed is slow

Question

I am using Laravel jQuery UI autocomplete to search through the database. It works but it's so slow. I know I have 38432 items in my database but I don't know if that is the problem. How do I make the search results come up faster?

public function aa(Request $request){

            $term = $request->get('term');
            $t=str_replace(" ", ", ", $term);
            $tt=str_replace(",", ", ", $term);



    //$queries=Profile::distinct('cityandstate','LIKE','%'.$term.'%')->take(5)->get();
    $queries = all::where('cityandstate', 'like',  $term . '%')->orWhere('cityandstate',$t)->orWhere('cityandstate',$tt)->take(10)->get();



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

    }       

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

}

JavaScript:

    <script type="text/javascript">
  $(document).ready(function(){

      $( function() {
          $( "#location-input" ).autocomplete({
            //html : html,
            source: "display-searches",
            minLength: 0,
            select:function(e,ui) { 
$("#location-input").val(ui.item.label);


            }
        } );
      } );

  });

    </script>

You haven't really given us much to go off of here. Maybe try posting some code snippets? In my experience the best ways to increase DB read speeds start with how the DB is set up, then how the query is formatted and finally, reducing inefficiencies in the business logic. — Ryan Gibbs, Feb 22 '18 at 00:49
There are a lot of different factors that can impact database performance. One of the biggest right away is to check your indexes to ensure they support the fields you are querying on or it will be difficult to scale. If you still need help on this as a question, please provide additional information on the schema ensuring to include any indexes. — Hazok, Feb 22 '18 at 02:07
I've already indexed it. I don't know what to do after I indexed it. here is my code query('CREATE Index Ix_cityandstate ON alls(cityandstate)'); if($connect){ echo"Created"; }else{ echo"Not created"; } ?> — , Feb 22 '18 at 02:29
Possible duplicate of [Laravel Jquery Auto complete speed](https://stackoverflow.com/questions/48913297/laravel-jquery-auto-complete-speed) — Andrew, Feb 22 '18 at 18:33
Three questions on exactly the same topic is the same as two deliberate duplicates, and that's the same as wasting people's time. That causes admin work to clean it up, and the possibility that several people will expend duplicate effort on each of your threads. One question per topic please. — halfer, Feb 23 '18 at 11:22

Marius Engen Haugen · Answer 1 · 2018-02-22T02:08:16.910

0

Eloquent is Laravel's active record pattern implementation. When you process a few records, it's truly awesome. But it comes with a performance price. I would recommend using Laravel's query builder for dealing with a large number of records. You would see an immediate performance boost. Also, it would help to index your database correctly, for the attributes you're querying on.

I also always install Laravel debugbar. It gives insight into what queries are running on the view you're visiting, and it also gives you time spent on each query, helping you narrow it down.

edited Feb 22 '18 at 02:08

answered Feb 22 '18 at 01:55

Marius Engen Haugen

960
8
15

i've indexed it already I dont know what to do after I indexed it here is how I indexed it query('CREATE Index Ix_cityandstate ON alls(cityandstate)'); if($connect){ echo"Created"; }else{ echo"Not created"; } ?> – Feb 22 '18 at 02:28
Marius, the OP has posted two other (essentially identical) questions about this, so they may be worth checking to see what answers have been given already. – halfer Feb 23 '18 at 11:25

Searching through database and the speed is slow

1 Answers1