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


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', 
                             ' as item_name', ' as item_unit_name',
                             ' as category_name', 
                              DB::raw('SUM(transaction_liquid_details.quantity * transaction_liquid_details.parfume) as total_sold'),
                              DB::raw('SUM( as total_price'))
                           ->join('items', '', '=', 'transaction_liquid_details.item_id')
                           ->leftJoin('item_units', '', 'items.item_unit_id')
                           ->leftJoin('categories', '', 'items.category_id')
                           ->leftJoin('transactions', '', 'transaction_liquid_details.transaction_id')
                           ->groupBy('transaction_liquid_details.item_id', 'items.barcode', '', '', '')
                           ->orderBy('total_sold', 'DESC')
                           ->whereBetween('transactions.created_at', [$start, $end . " 23:59:59"])
                           ->where('transactions.status', $this->FINISHED_TRANSACTION_STATUS)

And this is the table structure.

For items table.

For transaction_liquid_details table.


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?