Re: Make this faster?

[Björn Steinbrink]

You should have created a single index covering (year, month), not one index for each column ;) That should then get rid of the "Using filesort" as well. If that still isn't fast enough, extending the index to cover (year, month, md5_key) might help a bit, as it will allows MySQL to simply do an index scan instead of a table scan.

The second COUNT() should not use DISTINCT I think, but that depends on the result you expect...

Let's take a glance at a imaginary set of results of:
SELECT YEAR(files.date), files.md5_key, filesystem.md5_key FROM files LEFT JOIN filesystem ON files.md5_key = filesystem.md5_key


2005 1 NULL
2005 2 2
2005 2 2
2005 2 2
2005 3 3


The number of files for 2005 is:
COUNT(DISTINCT files.md5_key) == 3 ... Duplicates are ignored

Without DISTINCT it would result in:
COUNT(files.md5_key) == 5 ... No sure how to express what this value means ;)

The number of filesystem entries for those files is:
COUNT(filesystem.md5_key) == 4

The number of files that have a filesystem entry is:
COUNT(DISTINCT filesystem.md5_key) == 2

So choose those that match the expected result :)