MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:January 8 2007 3:05am
Subject:Re: Help optimizing this query?
View as plain text  
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: dkasak@stripped
website: http://www.nusconsulting.com.au
Thread
Help optimizing this query?Brian Dunning7 Jan
  • Re: Help optimizing this query?Daniel Kasak8 Jan
  • RE: Help optimizing this query?Michael Gargiullo8 Jan
    • Re: Help optimizing this query?Dan Buettner8 Jan
      • Re: Help optimizing this query?joce8 Jan