Can this query be optimised with indexes, or should I change my schema? I have a MySQL table that stores each time a user has downloaded a photo that they've bought.  I want to be able to retrieve the first time each photo was downloaded:

SELECT MIN(downloaded) AS downloaded
     , photoId
     , size
     , transactionId

FROM ORDER_DOWNLOADS

GROUP BY photoId, size, transactionId


I've got an index on photoId, size, transactionId, downloaded, as well as single-column indexes on each column.

With 50,000 rows this takes around a second.

Can I simply improve it substantially with a rewrite or a better index, or is my best bet to add a "firstDownload" bit column that I set to 1 upon an initial download and 0 on any subsequent downloads of the same photo/size/transaction?

Thanks!

explain:


id 1
select type SIMPLE
table ORDER_DOWNLOADS
partitons null
type index
possible keys photoId, size, transactionId, downloaded, covering
key covering
key_len 87
ref null
rows 43878 (I was being approximate when I said 50,000 earlier, this is the whole table)
filtered 100
Extra Using index
I think this request is fully optimized on a SQL point of view. Meanwhile, you may can increase performance by choosing a particular type of index depending on your data (for example BINARY for IDs), check this documentation for more details.

Moreover you have to keep in mind that the innoDB engine in MySQL (if you're using it) may be slow on a big amout of data.