Brian, can you post the output of EXPLAIN for your query? I.e., EXPLAIN <problem query> ;
At first glance, your query should be able to use the 'stamp-source' index since stamp is the first column indexed. However, I wonder if wrapping the "NOW() - INTERVAL 14 DAY inside a "DATE()" would help. MySQL may be casting your table data to a DATETIME for comparison, which would be resource intensive. Try this: stamp>=DATE(NOW()-interval 14 day) Your query will not be able to use the other index, 'country-source', because 'source' is the second column of the index and you are not querying against the 'country' column. If you do not need 'country' to be the first column (do you have other queries that use this index and query against country?), you could redefine the index to put 'source' first. As Michael pointed out you could also create a separate index on just 'source'. Or as brian suggests an index combining the two columns you're using here (I'd put 'source' first since you're doing a constant comparison with it). However, more indices = more overhead, which is sometimes a problem, sometimes not. Dan On 1/8/07, Michael Gargiullo <[EMAIL PROTECTED]> wrote:
-----Original Message----- From: Brian Dunning [mailto:[EMAIL PROTECTED] Sent: Sunday, January 07, 2007 1:12 PM To: mysql Subject: Help optimizing this query? This is the query that's killing me in the slow query log, usually taking around 20 seconds: select count(ip) as counted,stamp from ip_addr where stamp>=NOW()- interval 14 day and source='sometext' group by stamp order by stamp desc; Here is the table: CREATE TABLE `ip_addr` ( `ip` int(10) unsigned NOT NULL default '0', `stamp` date NOT NULL default '0000-00-00', `country` char(2) NOT NULL default '', `source` varchar(20) NOT NULL default '', PRIMARY KEY (`ip`), KEY `country-source` (`country`,`source`), KEY `stamp-source` (`stamp`,`source`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Any help please? :) --- Just a thought? Put a normal index on source and another on stamp (not combined). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
