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

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)..

CodePudding user response：

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

CodePudding user response：

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

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