Laravel orWhere（）/ MySQL或查询需要很长时间

Question

I'm using Laravel 4.2 and my application is used for tracking inventory across multiple locations.

The database is set up with an inventory_items table, inventory_locations table and a pivot table between them inventory_items_inventory_location, which contains the quantity values whilst referencing both the inventory item and location the record belongs to.

My query is to find inventory items that have any location quantity value more than or equal to 0. In Laravel I'm using a subquery and orWhere like so:

InventoryItem::whereHas('inventoryLocations', function($q) {
  $q->where('reserved', '>=', 0)
     ->orWhere('available', '>=', 0) # slow
     ->orWhere('inbound', '>=', 0) # slow
     ->orWhere('total', '>=', 0); # slow
})->toSql();

Which gives the following SQL:

select * from `inventory_items`
where `inventory_items`.`deleted_at` is null
and (
  select count(*) from `inventory_locations`
  inner join `inventory_item_inventory_location`
  on `inventory_locations`.`id` = `inventory_item_inventory_location`.`inventory_location_id` 
  where `inventory_item_inventory_location`.`inventory_item_id` = `inventory_items`.`id`
  and `reserved` >= ?
  or `available` >= ? # slow
  or `inbound` >= ? # slow
  or `total` >= ? # slow
) >= 1

The problem is that with the or statements (marked in the code by #slow) the query time is up to 1s directly with Sequel Pro, more than 5s through my Laravel app (or through artisan tinker). Without these 'or' checks (i.e. just checking for one quantity type e.g. 'reserved') the query is <100ms on Sequel Pro and similar on the app/tinker.

I'm not sure why adding these extra 'or' checks adds so much time to the query. Any ideas how to make a more performant query?

Answer 1

See the resulting query and its WHERE conditions. You definitely miss some brackets there, as I guess what you need is

where `inventory_item_inventory_location`.`inventory_item_id` = `inventory_items`.`id`
and (
   `reserved` >= ?
   or `available` >= ? #
   or `inbound` >= ?
   or `total` >= ?
)

instead of

where `inventory_item_inventory_location`.`inventory_item_id` = `inventory_items`.`id`
and `reserved` >= ?
or `available` >= ? # slow
or `inbound` >= ? # slow
or `total` >= ?

It results in full table scan which is terribly slow for tables with high amount of rows.

In order to fix that, replace

InventoryItem::whereHas('inventoryLocations', function($q) {
  $q->where('reserved', '>=', 0)
   ->orWhere('available', '>=', 0) # slow
   ->orWhere('inbound', '>=', 0) # slow
   ->orWhere('total', '>=', 0); # slow
})->toSql();

with

InventoryItem::whereHas('inventoryLocations', function($q) {
  $q->where(function($subquery) {
    $subquery->where('reserved', '>=', 0)
     ->orWhere('available', '>=', 0)
     ->orWhere('inbound', '>=', 0)
     ->orWhere('total', '>=', 0);
  });
})->toSql();

Check out MySQL's EXPLAIN command that lets you analyse how query will be executed and how many rows will be queried - http://dev.mysql.com/doc/refman/5.7/en/explain.html