Make this faster?

[Levi Tedder]

Hi

I hope I'm posting in the correct group, forgive me if this isn't so.

I'm having trouble making a search/query go faster, and hope to get some advice here.

I have these two tables (innodb):

CREATE TABLE "files" (
"md5_key" varchar(50) NOT NULL,
"date" datetime NOT NULL default '0000-00-00 00:00:00',
"type1" varchar(128) default NULL,
"type2" varchar(128) default NULL,
"counter" int(11) default NULL,
"filename" varchar(255) NOT NULL,
"text" varchar(255) NOT NULL,
PRIMARY KEY ("md5_key"),
KEY "type1" ("type1"),
KEY "type2" ("type2"),
KEY "date" ("date")
);

CREATE TABLE "filesystem" (
"md5_key" varchar(50) NOT NULL,
"deletes" varchar(255) NOT NULL,
"executes" varchar(255) NOT NULL,
KEY "md5_key" ("md5_key")
);


ALTER TABLE `filesystem`
ADD CONSTRAINT "filesystem_ibfk_1" FOREIGN KEY ("md5_key") REFERENCES "files" ("md5_key") ON DELETE CASCADE;

From this I want to list per month (ascending) total number of records in files and number of matching records in filesystem (unique count would be best but not important) where the column deletes is '1'.

I created this query:

SELECT Year(date), Month(date), Count(files.md5_key), Count(filesystem.md5_key),
CONCAT(monthname(date),' ',YEAR(date))
FROM files LEFT JOIN filesystem ON files.md5_key = filesystem.md5_key
AND deletes = '1'
GROUP BY Year(date), Month(date) order by date asc;

files has about 1 million records, and filesystem about 500k (not all files has filesystem "incidents"). The first time I run this query it takes about 15 seconds (status=copying to tmp table), second time faster since its cached.
Is there anything I can do with my tables or query to make this go faster?

Forgive me if I haven't explained it well enough, if so, just ask.

Thanks for any help/advice
Levi