Hi, I would use a (source,stamp) index, and not a (stamp,source) index to solve the performance problem.
Jocelyn Fournier www.mesdiscussions.net > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
