How to optimize query when operating with 1million++ data transaction details using laravel

Issue

This Content is from Stack Overflow. Question asked by Aditya Muhamad Putra P.

I want to make a report on the best-selling items and in the transaction_solid_details table, there are 1 million+ data. I am using Laravel query builder to get desired results from the database. The following query works perfectly but takes too much time to get results. Can you please help me optimize this query to make it faster?

$start = ($request->start) ? $request->start : '2022-04-01';
$end = ($request->end) ? $request->end : Carbon::now()->toDateString();

$bestSellingTransactions = DB::table('transaction_liquid_details')
                           ->select('transaction_liquid_details.item_id', 'items.barcode', 
                             'items.name as item_name', 'item_units.name as item_unit_name',
                             'categories.name as category_name', 
                              DB::raw('SUM(transaction_liquid_details.quantity * transaction_liquid_details.parfume) as total_sold'),
                              DB::raw('SUM(transaction_liquid_details.total) as total_price'))
                           ->join('items', 'items.id', '=', 'transaction_liquid_details.item_id')
                           ->leftJoin('item_units', 'item_units.id', 'items.item_unit_id')
                           ->leftJoin('categories', 'categories.id', 'items.category_id')
                           ->leftJoin('transactions', 'transactions.id', 'transaction_liquid_details.transaction_id')
                           ->groupBy('transaction_liquid_details.item_id', 'items.barcode', 'items.name', 'item_units.name', 'categories.name')
                           ->orderBy('total_sold', 'DESC')
                           ->whereBetween('transactions.created_at', [$start, $end . " 23:59:59"])
                           ->where('transactions.status', $this->FINISHED_TRANSACTION_STATUS)
                           ->get();

And this is the table structure.

For items table.
here

For transaction_liquid_details table.
here



Solution

Check the Answers

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?