Hi
Ok, if I understood correctly, I created this table:
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_month_ind" ("red_month"),
KEY "red_year_ind" ("red_year")
);
I then created a new query (without the check on the 'deletes' column:
SELECT red_year, red_month, Count(DISTINCT files.md5_key), Count(DISTINCT filesystem.md5_key),CONCAT(monthname(date),' ',YEAR(date))
FROM files LEFT JOIN filesystem ON files.md5_key = filesystem.md5_key
GROUP BY red_year, red_month
It still takes about 9 seconds to execute. Explain:
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,files,ALL,,,,,,,,912235,Using filesort
1,SIMPLE,filesystem,ref,md5_key,md5_key,52,db.files.md5_key,1,Using index
EDIT: The status of the connection is now "Sending data", not "copying to tmp table" as before.
Do you think it could be an issue with the mysql server itself? Some kind of settings not good? How fast do you think this should be?
Since I'm using PHP, I can split the query up in several steps if that could speed things up (one query using results from a previous one for example).
Thanks
Levi
Edited 1 time(s). Last edit at 01/05/2007 02:49AM by Levi Tedder.