Optimizing Eloquent Queries for Complex Reporting System in Laravel

Question

• I have multiple tables with millions of records, and I'm facing performance challenges with my current Eloquent queries.

• I have three tables = 'sales', 'expenses', and 'profits'

• Each table has columns such as 'amount', 'date', and 'category_id'

• I need to generate a report that shows total sales, total expenses, and net profit for each category on a monthly basis

• I'm currently using Eloquent to fetch data for each category and month, but the queries are becoming slow as the data get.

// Fetch total sales for a category in a specific month
$sales = Sale::where('category_id', $categoryId)
             ->whereMonth('date', $month)
             ->whereYear('date', $year)
             ->sum('amount');

• What strategies can I employ to optimize Eloquent queries for large datasets in Laravel?
• Are there specific indexing techniques or caching mechanisms I should consider for better performance?
• Is there a more efficient way to structure the database or queries to handle such reporting scenarios?

Answer 1

There are a lot of ways to improve performance. Firstly, you might want to add indexes to the category_id, date, and amount columns. Secondly, you can employ "chunking." Lastly, you can optimize your current query using caching.

// Fetch total sales for a category in a specific month
$sales = Cache::remember("sales-$categoryId-$month-$year", 60, function () use ($categoryId, $month, $year) {
    return Sale::where('category_id', $categoryId)
                ->whereMonth('date', $month)
                ->whereYear('date', $year)
                ->sum('amount');
});

Cache::remember method is used here to cache the query result. The first argument is a unique key for the cache. The second argument is the number of minutes to store the cache. The third argument is a Closure that contains the query. If the cache does not exist, the Closure will be executed, and its result will be stored in the cache..

Answer 2

Some points to consider when you are trying to optimize eloquent queries,

1. Always use the select() method when retrieving data from the table. Only retrieve the columns that you need. For example, you have a table named sales and you only need 2 columns, categoty_id and date that will be used then your query should be,

Sale::select('id','categoty_id', 'date')->get();

2. If your APIs that use fetch queries are called often then try to cache the query result. You can use Redis, or Memcache for caching so your database will have less load.

3. Use eager loading instead of lazy loading to reduce the number of queries.

4. Always use keys like foreign keys, unique keys, etc., and also do indexing wherever required. For example, if columns like category_id and date are used frequently then make them indexed.

5. Use laravel telescope to check query performance. It's also useful to track other things like cache, logs, events, and broadcasting. But remember you should only use it in development because it can slow your database in production.