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?
- 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