Hello,
I'm after some performance advice if anyone has some. I've a table and query described below. The table holds about 13 million records. Query times for the query are about 135 seconds, which is way to long for live queries. Does anyone have any suggestions on how to reduce this. I realise live queries to a database this size might have to be tackled by spliting into other tables etc, but other sites manage this, and I just don't know how. I have already sumerised the data as much as possible to minimise the rows.
dns_stats CREATE TABLE `dns_stats` (
`hits` mediumint(11) unsigned default '0',
`domainname` varchar(100) NOT NULL default '',
`datetime` date NOT NULL default '0000-00-00',
`server` tinyint(4) unsigned NOT NULL default '1',
KEY `datetime` (`datetime`),
KEY `datetimedn` (`domainname`(20),`datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select domainname, sum(hits) as thits,server, datetime from dns_stats where domainname like '.moc.tsettsrif%' and datetime >= DATE_SUB(NOW(),INTERVAL 30 DAY) group by datetime,server order by datetime
and explain shows
1,SIMPLE,dns_stats,range,datetime\,datetimedn,datetimedn,23,\N,7925,Using where; Using temporary; Using filesort
This table gets updated each night, so is only read from during the day. The queries are almost exclusivly reads. I tried tests without the summary and grouping but this only takes about 10 seconds off, so I'm guessing almost all the time is spent retrieving the initial records.
Any suggestions appreciated.
Thanks.
(I cross posted this to the Mysql forum)
I'm after some performance advice if anyone has some. I've a table and query described below. The table holds about 13 million records. Query times for the query are about 135 seconds, which is way to long for live queries. Does anyone have any suggestions on how to reduce this. I realise live queries to a database this size might have to be tackled by spliting into other tables etc, but other sites manage this, and I just don't know how. I have already sumerised the data as much as possible to minimise the rows.
dns_stats CREATE TABLE `dns_stats` (
`hits` mediumint(11) unsigned default '0',
`domainname` varchar(100) NOT NULL default '',
`datetime` date NOT NULL default '0000-00-00',
`server` tinyint(4) unsigned NOT NULL default '1',
KEY `datetime` (`datetime`),
KEY `datetimedn` (`domainname`(20),`datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select domainname, sum(hits) as thits,server, datetime from dns_stats where domainname like '.moc.tsettsrif%' and datetime >= DATE_SUB(NOW(),INTERVAL 30 DAY) group by datetime,server order by datetime
and explain shows
1,SIMPLE,dns_stats,range,datetime\,datetimedn,datetimedn,23,\N,7925,Using where; Using temporary; Using filesort
This table gets updated each night, so is only read from during the day. The queries are almost exclusivly reads. I tried tests without the summary and grouping but this only takes about 10 seconds off, so I'm guessing almost all the time is spent retrieving the initial records.
Any suggestions appreciated.
Thanks.
(I cross posted this to the Mysql forum)