Optimize eloquent queries-CodePudding

I have to get stats from database like this

$stats = [
    'products_count' => Product::where('status', 1)->count(),
    'last_year_products_count' => Product::whereYear('created_at', now()->subYear()->year)->count(),
    'last_year_order_items_count' => OrderItem::whereYear('created_at', now()->subYear()->year)->sum('quantity'),
    'order_item_unique_count' => OrderItem::distinct('product_id')->count(),
    'last_year_order_item_unique_count' => OrderItem::whereYear('created_at', now()->subYear()->year)->distinct('product_id')->count(),
    'order_products_avg' => OrderItem::avg('quantity'),
    'last_year_order_products_avg' => OrderItem::whereYear('created_at', now()->subYear()->year)->avg('quantity'),
];

I want optimize sql queries most as possible

CodePudding user response：

As said in the comment is better to index your specific columns. You will need to add following logic in migration

For products table:

$table->index(['status', 'created_at']);

For order_items table:

$table->index(['created_at']);

For more information about database indexing in Laravel: check out

For more information about database indexing at all: check out

CodePudding user response：

I think it's bad idea use query for every field

Try get all data by 1-2 query and parse necessary information like:

$product = Product::where('status', 1)
    ->whereYear('created_at', now()->subYear()->year)

And this array look too complicated, mb you should decompose this array