Re: Make this faster?

[Björn Steinbrink]

Adding (redundant) columns for year and month plus an index on those columns, ie. (year, month) should allow MySQL to utilize that index for the group by operation. You should also drop the order by part in that query. Because of the group by, only the year and month parts of it are actually meaningful and MySQL will order by those anyway, because group by includes ordering and ordering by a different column always forces a file sort.

That said, I'm not sure if COUNT(files.md5_key) is really what you want. IIRC, because of the join, that should give you the number of row in the files table times the number of matching rows in the filesystem table for each file. I'd expect to see COUNT(DISTINCT files.md5_key) there instead.