Question 249962 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Difference in time taken by same query

I am facing a problem with a SQL query, which takes around 500ms when queried using RAW SQL (retrieve all data without limit). However, using the same query in laravel(tried Eloquent, DB builder and RAW) it takes around 15 seconds to fetch the data.

Laravel Query:

$date = "2023-06-01";
$placeholders = implode(',', array_fill(0, count($placeholderValues), '?'));

$query = "SELECT * FROM my_table WHERE table_column IN ($placeholders) AND date > '$date'";
$result = DB::select(DB::raw($query),$placeholderValues);

RAW Query:

SELECT * FROM my_table WHERE table_column IN (
 //placeholders
) AND  date > "2023-06-01";

I have tried to use DB builder and used join instead of WHEREIN but there’s no imporvement. Indexing the column drops the query time to around 3-4 seconds but I think indexing is not only the efficient approach to optimize this query as production DB has millions of records and grows everyday, and the above mentioned tests were performed on dev server with less data.

Any help is much appreciated.

Tags:

2

Answers


  1. 0

    While the same query is being run both times, Laravel is building a collection of these "millions of rows" in run time which is likely why there is such a large time disparity between this.

    If we use the chunk method (https://laravel.com/docs/10.x/queries#chunking-results) then behind the scenes we can pull in this data bit-by-bit.

    From The Docs

    DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    foreach ($users as $user) {
        // ...
    }
});

    Behind the scenes we have some nice magic to reduce memory consumption and could likely improve speed dramatically.

    Generally this approach is best combined with async jobs, allowing processing in the background and feeding back to where is necessary.

    You should also select only the fields you need. A select * ... statement, as shown in your example, may not be neccesary and you could likely cut down on how much data is being brought back.

    Login or Signup to reply.
  2. 0

    I recommend adding two indexes. (The optimizer may use one of them to speed up the query.)

    INDEX(date)
INDEX(table_column)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search