Question 63968 in PhpMyAdmin Question posted in
The official documentation can be found here.

Simple query is slow in laravel (with paginate) but very fast in phpmyadmin

I’ve looked at similar questions, and the answers I get is to put limit in my query, which I did.

Here is the query

  $sql = DB::table('inventorysku_tb as isku')
     ->leftJoin('inventorytrackingmodules_tb as itm', function ($join) {
        $join->where('itm.coID', '=', 4)
           ->whereBetween('itm.effectiveDate', ['2021-05-01', '2021-05-31'])
           ->on('itm.skuID', '=', 'isku.ID');
     })


  $sql->paginate(25);

  return $sql;

This query takes around 17 seconds to run, now if I run ->toSql() on the query above and paste it in phpmyadmin (changing the ? values)

select * 
from `inventorysku_tb` as `isku` 
    left join `inventorytrackingmodules_tb` as `itm` 
        on `itm`.`coID` = 4
        and `itm`.`effectiveDate` between '2021-05-01' and '2021-05-31'
        and `itm`.`skuID` = `isku`.`ID`

This one only takes 0.2 seconds to run in phpmyadmin…

That is all on the laravel code, there are no other codes above it…

Another weird thing is if I change to left join to inner join in laravel, the query is now fast (878 ms)..

Tags:

2

Answers


  1. 0

    according to Laravel doc:
    On clauses can be chained, e.g.

    $join->on('contacts.user_id', '=', 'users.id') ->on('contacts.info_id', '=', 'info.id')

    will produce the following SQL:

    on contacts.user_id = users.id and contacts.info_id = info.id

    so, for you case the code could be:

    $sql = DB::table('inventorysku_tb as isku')
     ->leftJoin('inventorytrackingmodules_tb as itm', function ($join) {
        $join->on('itm.coID', '=', 4)
           ->on(function ($query){
                $query->whereBetween( 'itm.effectiveDate',['2021-05-01',
                   '2021-05-31']);
           ->on('itm.skuID', '=', 'isku.ID');
     })

    this way, I think you could produce the same query that you used in phpMyAdmin

    Login or Signup to reply.
  2. 0

    Remove LEFT. Since you are limiting things in the WHERE it is a JOIN.

    Add this to itm: INDEX(coID, skuID, effectiveDate)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search