How to optymalize eloquent query with large datasets

Hello Guys,

I have query for calculate turnover by table documents. The problem came at a time when there was a lot of data.

How can I optimize my query?

The query must be performed every time to keep the data up-to-date, maybe apart from optimizing the query, I should use the cache and keep the data for 60 minutes?

$turnover = Document::where('shop_id', $shop_id)
            ->whereBetween('doc_date', [$startDate, $endDate])
            ->whereIn('doc_type', self::DOC_TYPE)
            ->selectRaw('sum(net_selling_price*quantity) AS sum_turnover')
            ->groupByRaw('DATE(doc_date)')
            ->get();

        return $turnover->avg('sum_turnover') ?? '0.00';

Tray2

4 years ago

Level 73

The first thing I would do is replace the ->get() with a ->toSql() then copy the query to Table plus or some other database editor and run an explain plan on the query.

It will most likely tell you that it runs a full table scan on a few of the parts of the query. On those parts you should add an index to make remove the full table scan if possible.

I would start with adding an index on doc_date and probably doc_type

Sencond you should look for any carthesian products since they are usually a bad sign.

Please or to participate in this conversation.