Brian Dunning wrote:
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? :)
You could create an index that contains the fields in your where clause
( ie 'stamp' and 'source' ).
Also, I'm not sure how MySQL handles that:
where stamp >= NOW() - interval 14 day
part. Does it calculate NOW() - interval 14 day for each record? It's
possible that it does. You could try doing this in a separate query /
calculation, and then pass the result into the query. Anyway,
investigate it ... as I said, I'm not sure how MySQL handles this. Maybe
others can comment.
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
