Thinking about it, this is a pretty slow type of query to run in the first place as is has to scan the whole of the left ('files') table to run (then do an index lookup for each md5_key value in the right ('filesystem') table. You can't help but look at lots of rows and it's never going to be all that fast.
Quote
Do you mean I should create an index with md5_key,year,month as well?
Nope, I made a mistake on a test table I made - you shouldn't need to do this. Your query looks to be optimising pretty well now as it is.
Quote
Now when I execute the query it takes a while the first time, but the second time it's cached and it's fast. If I add some records, the query slows down again. Maybe the "primary index" is rebuilt or something on each insert?
Do you have the query cache turned on? Try 'select sql_no_cache ...' to see how it behaves then. You should see that subsequent runs are faster, and you may well see that adding a few rows slows things down, but it shouldn't by too much. With the query cache turned on, modifying the table at all will invalidate the cache and force the query to re-run fully so you'll see a massive execution time difference.
Quote
Or maybe I should create a new table which stores counts for each month from files, filesystem and the other tables I use. And this is updated each time a new record is inserted...
Sometimes summary tables are the only way to go. That might well be the case here if you need this query to run in much less than 9 seconds.
HTH a bit anyway. :)
Toasty
-----------------------------------------
email: 'toasty'*3 at gmail