How to optimize SQL query with LEFT JOIN?

Question

Search Engine Optimization

There is a sqlite database and two tables
in the first (requests) all sorts of data (about 26000)
in the second (requestrewards) requestid and number (about 125000 records)
you need to select the five largest requests by the amount of requestrewards
The code below does this in more than 1 second

SELECT SUM(value) as `total`, requestid, R.*, R.rowid as id
FROM `requestrewards`
LEFT JOIN `requests` as R ON R.rowid = requestrewards.requestid
GROUP BY `requestid` ORDER BY `total` DESC LIMIT 5

I really want to optimize, because real data is needed. Cash won't work.

Answer 1

Have you tried to create indexes on the columns on which you do left outer join ?

Answer 2

Good afternoon!
And if you try to get group 2 tables at the beginning, thereby get the sum (as I understand it, requestid is not a unique identifier). Then select top 5 from the resulting query and join from the first table.

Answer 3

try like this:

select total, requestid, R.*, R.rowid as id
from 
(
select sum(value), requestid from requestrewards
group by requestid
order by total
) as rr
LEFT JOIN `requests` as R ON R.rowid =rr.requestid

although the problem is in R.*. Are you sure you need all the fields from that table?

Answer 4

While solved a problem the trigger on record. But this is not a solution but a crutch.