MySQL Forums
Forum List  »  Optimizer & Parser

Re: Make this faster?
Posted by: Levi Tedder
Date: January 05, 2007 02:40AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
3729
January 04, 2007 07:38AM
2264
January 04, 2007 10:21AM
2222
January 04, 2007 11:51AM
2329
January 04, 2007 03:34PM
Re: Make this faster?
2305
January 05, 2007 02:40AM
2227
January 05, 2007 05:07AM
2223
January 05, 2007 06:55AM
2256
January 05, 2007 08:31AM
2275
January 05, 2007 08:33AM
2440
January 05, 2007 08:45AM
2238
January 05, 2007 08:31AM
2282
January 05, 2007 08:45AM
2360
January 05, 2007 03:11PM
2259
January 08, 2007 02:44AM
2199
January 08, 2007 04:28AM
2242
January 08, 2007 04:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.