Improve Laravel Eloquent Query-CodePudding

I have this relation in my model...

$this->hasMany('App\Inventory')->where('status',1)
            ->whereNull('deleted_at')
            ->where(function($query){
                $query
                    ->where('count', '>=', 1)
                    ->orWhere(function($aQuery){
                        $aQuery
                            ->where('count', '=' , 0)
                            ->whereHas('containers', function($bQuery){
                                $bQuery->whereIn('status', [0,1]);
                            });
                    });
            })
            ->orderBy('updated_at','desc')
            ->with('address', 'cabin');

And Sql query generated are :

select
  *
from
  `inventories`
where
  `inventories`.`user_id` = 17
  and `inventories`.`user_id` is not null
  and `status` = 1
  and `deleted_at` is null
  and (
    `count` >= 1
    or (
      `count` = 0
      and exists (
        select
          *
        from
          `containers`
        where
          `inventories`.`id` = `containers`.`inventory_id`
          and `status` in (0, 1)
      )
    )
  )
  and `inventories`.`deleted_at` is null
order by
  `updated_at` desc
limit
  10 offset 0

Unfortunately this take more than 2sec in MySql,

There are anyways to improve and reduce the query time for this?!

Each inventory has many containers. when inventory count is 0 (0 mean out of stock but sometimes there are disabled containers that mean inventory is not out of stock yet.) the real count is depend on count of containers with status [0,1] (containers have other statuses...).

I have an idea to have a column on inventory to count containers with [0,1] status, and update it in other processes to improve this query. but this take too much time and need to modify other process.

Inventories show create table

CREATE TABLE `inventories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `cabin_id` bigint unsigned NOT NULL,
  `address_id` bigint unsigned NOT NULL,
  `count` mediumint NOT NULL,
  `status` mediumint NOT NULL,
  `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `available_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37837 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Containers show create table

CREATE TABLE `containers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inventory_id` bigint unsigned NOT NULL,
  `order_id` bigint unsigned DEFAULT NULL,
  `status` tinyint unsigned NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=64503 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CodePudding user response：

when your data is big, whereHas statement sometimes run slowly because it use exists syntax. For more detailed explanation, you can read from this post.

To solve this, I prefer you to use mpyw/eloquent-has-by-non-dependent-subquery because it will use in syntax which will improve the performance. I already used this package on my project, and no problem until now.

CodePudding user response：

One way to improve the performance of your query is to use subqueries instead of the whereHas function. This will allow the database engine to optimize the query better by performing the subquery first and then using the results to filter the main query.

Here is an example of how you can rewrite your query using subqueries:

$this->hasMany('App\Inventory')->where('status', 1)
    ->whereNull('deleted_at')
    ->where(function ($query) {
        $query->where('count', '>=', 1)
            ->orWhere('count', '=', 0)
            ->whereIn('id', function ($subquery) {
                $subquery->select('inventory_id')
                    ->from('containers')
                    ->whereIn('status', [0, 1]);
            });
    })
    ->orderBy('updated_at', 'desc')
    ->with('address', 'cabin');