Hi
My table is now like this:
CREATE TABLE "files" (
"md5_key" varchar(50) NOT NULL,
"date" datetime NOT NULL default '0000-00-00 00:00:00',
"red_month" int(11) default NULL,
"red_year" int(11) default NULL,
"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"),
KEY "red_year" ("red_year","red_month")
);
Query:
SELECT red_year, red_month, Count(DISTINCT files.md5_key), Count(DISTINCT filesystem.md5_key),CONCAT(monthname(date),' ',red_year)
FROM files LEFT JOIN filesystem ON files.md5_key = filesystem.md5_key
GROUP BY red_year, red_month
Explain:
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,files,index,,red_year,10,,927794,
1,SIMPLE,filesystem,ref,md5_key,md5_key,52,db.files.md5_key,1,Using index
Time of execution is about 9 seconds. Status is "sending data" (does this mean anything?)
md5_key is primary in files. Do you mean I should create an index with md5_key,year,month as well?
Maybe this is as fast as I can get it? Maybe I need some tuning on the server to get it faster now?
Thanks for your effort
Levi