John Davidson

Message:

I have 2 tables transactions and users tables which both have over 100k records.
In my transaction table, i have amount as one of my columns which i want to sum up and order by the highest amount, but it takes longer time to fetch records.

$data = DB::table('transactions')

->join('users', 'users.uuid', '=', 'transactions.user');



$data->select(DB::raw('sum(transactions.amount) as amount, transactions.user, transactions.rate, transactions.currency, transactions.updated_at, transactions.created_at, users.firstname, users.lastname, users.username'));



$data->groupBy('transactions.user')

->orderByRaw('SUM(amount) desc');

$datas = $data->take(15)->get();

Please help! I dont want to use "hasMany" or "belongsTo" or "with" because I don't know much about it

---

My Answer:
>select('user_id', DB::raw('SUM(amount) as total_amount'))
>groupBy('user_id')
>orderBy('total_amount', 'desc')
>get();

This query is fetching all the records from the transactions table, grouping them by user_id, summing up the amount for each user, and then ordering the results by the total amount in descending order.

To optimize the query and reduce the time it takes to fetch records, you can consider the following:

1. Indexing: Make sure that the user_id column in the transactions table is indexed. This will help speed up the grouping and ordering operations.

2. Limit the number of records: If you only need to fetch a certain number of records, you can use the `take()` method to limit the number of results returned.

3. Eager loading: If you need to fetch related data from the users table, you can use eager loading to reduce the number of queries executed.

4. Use raw queries: If the query is still taking too long, you can consider writing a raw SQL query to optimize the performance further.

By implementing these optimizations, you should be able to reduce the time it takes to fetch records from the transactions table.