1

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
  • 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 '17 at 9:37
  • It uses the index but returns all 50,000 rows, I presume because of the MIN aggregate? – Codemonkey Oct 17 '17 at 9:46
  • Added explain to original question. – Codemonkey Oct 17 '17 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 '17 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 '17 at 22:46
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.

| improve this answer | |
  • 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 '17 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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