Laravel Eloquent Query to 2 Million Rows takes long time

Question

I have to pull data from a table that has 2 million rows. The eloquent query looks like this:

$imagesData = Images::whereIn('file_id', $fileIds)
                    ->with('image.user')
                    ->with('file')
                    ->orderBy('created_at', 'DESC')
                    ->simplePaginate(12);

The $fileIds array used in whereIn can contain 100s or even 1000s of file ids.

The above query works fine in small table. But in production site that has over 2 million rows in Images table, it takes over 15 seconds to get a reply. I use Laravel for api only.

I have read through other discussions on this topic. I changed paginate() to simplePaginate(). Some suggests perhaps having a DB:: query with whereRaw might work better than whereIn. Some says it might be due to PDO in php while processing whereIn and some recommends using Images::whereIn which I already used.

I use MariaDB, with InnoDB for db engine and its loaded into RAM. The sql queries performs well for all other queries, but only the ones that has to gather data from huge table like this takes time.

How can I optimise the above laravel query so I can reduce down the query response to couple of seconds if possible when the table has millions of rows?

Answer 1

You need indexing, which segmented your data by certain columns. You are accessing file_id and created_at. Therefore this following index will help performance.

$table->index(['file_id', 'created_at']);

Indexing will increase insert time and can make queries have weird execution plans. If you use the SQL EXPLAIN on the query before an after executing the query, we can verify it helps the problem.

Answer 2

Here is an update on steps taken to speed up the page load.

1. The real culprit for such a really slow query was not the above specific query alone. After the above query fetches the data, the php iterates over the data and does a sub-query inside it to check something. This query was using the filename column to search the data during each iteration. Since filename is string and not indexed, the response time for that controller took so long since each sub-query is crawling through 1.5 millions rows inside a foreach loop. Once I removed this sub-query, the loading time decreased by a lot.

2. Secondly, I added the index to file_id and created_at as suggested by @mrhn and @ceejayoz above. I created a migration file like this:

   Schema::table('images', function (Blueprint $table) {
       $table->index('file_id', 'created_at');
   });        
   

3. Optimised the PHP script further. I removed all queries that does searches using fileNames and changed it to use the id to fetch results. Doing this made a huge difference throughout the app and also improved the server speed due to less CPU work during peak hours.

4. Lastly, I optimised the server by performing the following steps:

   • Completed any yum updates
   • Updated Litespeed
   • Tweaked Apache profile, this included some caching modules and installed EA-PHP 7.3
   • Updated cPanel
   • Tuned Mysql to allow your server to utilize some more of your server resources.

Once I edit all of the above steps, I found a huge difference in the loading speed. Here are the results:

BEFORE:

AFTER:

Thanks to each and everyone who have commented on this. Your comments helped me in performing all of the above steps and the result was fruitful. Thanks heaps.