分単位でログ集計するためカウントする方法

質問

アクセスログの分析のため月ごとで、データをlog_siteというテーブルに入れ込んでいます。(データ量は約4500万件)
※実際はもっと項目(リクエストURL、HTTPステータス、UAなど)がありますが、
ここでは省略しています。

+----+-----------------+--------------------+----------+----+------+
+ No | IP | DATETIME | YYYYMMDD | HH | HHMM |
+----+-----------------+--------------------+----------+----+------+
+ 1 | XXX.XXX.XXX.XXX | 2012-11-14 18:50:11| 20121114 | 18 | 1850 |
+ 2 | XXX.XXX.XXX.XXX | 2012-11-14 11:44:04| 20121114 | 11 | 1144 |
+ 3 | XXX.XXX.XXX.XXX | 2012-11-14 17:16:06| 20121114 | 17 | 1716 |
+ 4 | XXX.XXX.XXX.XXX | 2012-11-03 16:47:01| 20121103 | 16 | 1647 |
+ 5 | XXX.XXX.XXX.XXX | 2012-11-05 19:35:18| 20121105 | 19 | 1935 |
+ 6 | XXX.XXX.XXX.XXX | 2012-11-03 16:52:39| 20121103 | 16 | 1652 |
+ 7 | XXX.XXX.XXX.XXX | 2012-11-04 15:19:40| 20121104 | 15 | 1519 |
+ 8 | XXX.XXX.XXX.XXX | 2012-11-12 12:34:58| 20121112 | 12 | 1234 |
+ 9 | XXX.XXX.XXX.XXX | 2012-11-09 09:50:53| 20121109 | 09 | 0950 |
+ 10 | XXX.XXX.XXX.XXX | 2012-11-09 13:08:53| 20121109 | 13 | 1308 |
+ 11 | XXX.XXX.XXX.XXX | 2012-11-08 06:57:31| 20121108 | 06 | 0657 |
+ 12 | XXX.XXX.XXX.XXX | 2012-11-13 11:12:14| 20121113 | 11 | 1112 |
+ 13 | XXX.XXX.XXX.XXX | 2012-11-05 10:22:42| 20121105 | 10 | 1022 |
+----+-----------------+--------------------+----------+----+------+

各項目は以下のように定義しています。

`No` int(11) NOT NULL AUTO_INCREMENT,
`IP` varchar(15) DEFAULT NULL,
`DATETIME` datetime DEFAULT NULL,
`YYYYMMDD` varchar(8) DEFAULT NULL,
`HH` varchar(2) DEFAULT NULL,
`HHMM` varchar(4) DEFAULT NULL,

やりたいこととしては、1分または1時間単位でアクセスを集計しグラフを書こうとしています。
グラフに対しては、43,56,35,・・,5といった状態で渡せばグラフを書いてくれるのですが、
そのデータを取り出すため以下のようなSQLを書いて、各1分単位でそのカウントをして見ました。

select HHMM, count(IP) from log_site
where YYYYMMDD='20121101'and (
(HHMM='0000')or(HHMM='0001')or(HHMM='0002')・・・・(HHMM='2359')
)group by HHMM;

+------+-------+
+ HHMM | count |
+------+-------+
+ 0001 | 43 |
+ 0002 | 56 |
+ 0003 | 35 |
(----省略----)
+ 2359 | 5 |
+------+-------+

アクセス時間については、DATETIMEの項目にデータを入れているのですが
1分ずつ、1時間ずつ集計するのに日時や秒の情報が邪魔だったので
YYYYMMDD、HH、HHMMといった内容で情報を持たせています。

実行した結果、取得できるのに300-400秒必要なのですが、
1日を取得するだけで、5-7分要するのは、なんとかならないでしょうか？
最低でも3日、最高で7日間ぐらいのデータは取得したいと考えているため、
この時間はかかりすぎなのです。

やはりログデータ量が約4500万件あるという多さから予め日ごとで集計された
数字を別テーブルに持たせることを考えていたのですが、
引っ掛けたいリクエストURLや、HTTPステータス、UAなど他の項目でも
アクセス傾向が知りたいため、予め日ごとで集計された結果を別テーブルに持つ
ということは出来ないので、予め集計された結果を持っておく訳にも行かず困っています。

もし今の状況でどのようなＳＱＬだったら、データをスムースに取得できるのでしょうか？

またスムーズにデータを取得するためにデータ構造の見直しが必要としたら
どのように変えて、かつどのようにｓｑｌを組んで取得すればよいのでしょうか？
（仮に、日ごとでテーブルを作った場合は1日あたり150万程度になりますが
日をまたぐ場合は、どのようなSQLになるのでしょうか？）

もっといい方法があれば教えていただければと思います。
よろしくお願いいたします。

回答No.2

ki073

まったくSQLを使っていませんが、logがそのまま残っているのなら、このような方法があります。
cut -c1-19 <log.log|uniq -c >result.txt
日付時刻部分を切り出してuniq -cで行数を数えるものです。
2日分300万件の疑似ログデータを作成して8秒で集計できました。
除外したいデータが有る場合にはgrepなどを入れる必要があるのでもう少し時間がかかりますが。

ご参考に

回答No.1

yambejp

とりあえずインデックスは？
あとデータの持ち方と集計方法を工夫したほうが

CREATE TABLE `log_site`(
`No` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`IP` VARCHAR(15),
`DATETIME` DATETIME,
`YYYYMMDD` DATE,
`HH` TIME,
`HHMM` TIME);

ALTER TABLE `log_site` ADD INDEX(`YYYYMMDD`,`HHMM`);

INSERT INTO `log_site`(`IP`,`DATETIME`) VALUES(
'XXX.XXX.XXX.XXX','2012-11-14 18:50:11'),(
'XXX.XXX.XXX.XXX','2012-11-14 11:44:04'),(
'XXX.XXX.XXX.XXX','2012-11-14 17:16:06'),(
'XXX.XXX.XXX.XXX','2012-11-03 16:47:01'),(
'XXX.XXX.XXX.XXX','2012-11-05 19:35:18'),(
'XXX.XXX.XXX.XXX','2012-11-03 16:52:39'),(
'XXX.XXX.XXX.XXX','2012-11-04 15:19:40'),(
'XXX.XXX.XXX.XXX','2012-11-12 12:34:58'),(
'XXX.XXX.XXX.XXX','2012-11-09 09:50:53'),(
'XXX.XXX.XXX.XXX','2012-11-09 13:08:53'),(
'XXX.XXX.XXX.XXX','2012-11-08 06:57:31'),(
'XXX.XXX.XXX.XXX','2012-11-13 11:12:14'),(
'XXX.XXX.XXX.XXX','2012-11-05 10:22:42');

UPDATE `log_site`
SET `YYYYMMDD`=DATE(`DATETIME`)
,`HH`=CONCAT(HOUR(`DATETIME`),':00:00')
,`HHMM`=CONCAT(HOUR(`DATETIME`),':',MINUTE(`DATETIME`),':00')
WHERE `YYYYMMDD` IS NULL;

SELECT `HHMM`, COUNT(*) AS COUNT
FROM `log_site`
WHERE `YYYYMMDD`='2012-11-03'
GROUP BY `HHMM`;