Mysql – Can this query be optimised with indexes, or should I change the schema

indexMySQLoptimization

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

Best Answer

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.

Related Question