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


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
  • 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
  • Did you do an explain of the SQL to see how the result is fetched? Did it do a full table scan or did it use the indexes?
    – Marco
    Oct 17, 2017 at 9:37
  • It uses the index but returns all 50,000 rows, I presume because of the MIN aggregate?
    – Codemonkey
    Oct 17, 2017 at 9:46
  • Added explain to original question.
    – Codemonkey
    Oct 17, 2017 at 9:50
  • 1
    Provide us with the output of SHOW CREATE TABLE ORDER_DOWNLOADS\G - although 1s for 50k records doesn't appear too shoddy!
    – Vérace
    Oct 17, 2017 at 11:28
  • Sounds like a "cold" cache. Run it twice. How many rows in the resultset? What is the value of innodb_buffer_pool_size?
    – Rick James
    Oct 17, 2017 at 22:46

1 Answer 1


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.

  • I am using innoDB, but I don't think we can call < 50,000 rows a big amount of data can we?
    – Codemonkey
    Oct 17, 2017 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.