How to make GROUP BY use index?

Question

I have a very simple query:

SELECT SUM(amount) sum, psp_id
FROM pos_transactions
GROUP BY psp_id

It takes over 10sec to be executed. Also here is the result of EXPLAIN:

Noted that, there is an index defined on pos_transactions(psp_id) as well. Any idea how can I make it optimal?

Answer 1

You can add an index with both psp_id and amount:

create index psp_amount_ind on pos_transactions (psp_id, amount)

If you only have an index with psp_id, MySQL needs to fetch the amount from the row. Using an index in that case is not useful as it can also get the psp_id from the row.

See dbfiddle