CODEWITHSUNDEEP
mysqllaravelperformanceeloquent
Stephanos Papastylianou
Stephanos Papastylianou

Reputation: 169

Laravel eloquent too slow with large amount of data

I am using Laravel 7.0 and MySQL 5.7.

I am using Eloquent to fetch records from the database and when the database contains a large amount of data the response time becomes extremely slow.

A few examples are shown below:

Movie::where('title', 'LIKE', '%'.$search.'%')->take(10)->get(); to get the first 10 results that match a search string.

Movie::orderBy('rating', 'desc')->take(10)->get() to get the 10 top rated movies.

Person::has('acted')->orderBy('popularity', 'desc')->take(10)->get() to get the 10 most popular actors.

When I use a test database of ~1000 records the response time is acceptable, but when I switch to the live database of ~500,000 records it's horrible.

Any suggestions on how to improve the performance are appreciated!

Upvotes: 3

Views: 7572

Answers (2)

Rick James
Rick James

Reputation: 142208

LIKE '%...' is very inefficient. Each row must be checked by scanning the text.

Much better is the use of

FULLTEXT INDEX(title)

and 

WHERE MATCH(title) AGAINST('+$search')

(Sorry, I don't know how to turn that into Laravel.)

Movie::orderBy('rating', 'desc')->take(10)->get()

That is probably helped by 

INDEX(rating)

Upvotes: 2

emotality
emotality

Reputation: 13035

I don't think anyone will need to see 500,000 results in a single page, you would need to break it into parts and paginate the results. You can also use chunk, or use the cursor method. Choose one that would fit your needs best.

If this is to be viewed by the user, then I would suggest to paginate the results. This way the user can go to the next page and the next page etc. Or click on "Load More" button to make a request to your API to load page 2 then page 3 etc.

If this is just to process data for manipulation or update values, I would suggest using chunk or cursor.

To paginate: 

Movie::where('title', 'LIKE', '%'.$search.'%')->paginate(20);

This will fetch 20 items per page.

Source: https://laravel.com/docs/pagination#introduction

To chunk: 

Movie::where('title', 'LIKE', '%'.$search.'%')->chunk(200, function ($movies) {
    foreach ($movies as $movie) {
        //
    }
});

This will fetch 200 at a time then loop through them.

Source: https://laravel.com/docs/eloquent#chunking-results

To use cursor method:

foreach (Movie::where('title', 'LIKE', '%'.$search.'%')->cursor() as $movie) {
    //
}

This will iterate through your database records using a cursor, which will only execute a single query.

Source: https://laravel.com/docs/eloquent#chunking-results

Laracasts has a 10min video of it explained in detail.

Upvotes: 2

Related Questions