статистический вопрос

[belbek]

статистический вопрос

таблица
id ip url created_at
1 1 1 10:00
2 1 1 11:00
3 2 1 12:00
3 3 1 13:00
3 3 2 14:00
4 3 2 15:00
5
6


узнать сколько времени определенный ip пребывал на определенном url
таблица более 100000 поэтому запрос вида

select a.url, a.ip, a.created_at, round( time_to_sec( timediff( if( b.created_at, b.created_at, a.created_at + 500 ), a.created_at ) )/60 ) as diff from stat as a left join stat as b on a.id = b.id - 1 group by a.ip, a.url order by a.created_at asc

работает но не катит. Окончания выполнения на реальной таблице не дождался, может кто подскажет обходные пути? Спасибо.

 

[Wicked]

explain для начала

-~{}~ 18.05.10 18:05:

и разве в условии on не должно быть a.id = b.id - 1 and a.ip = b.ip ?

 

[belbek]

a.id = b.id - 1 and a.ip = b.ip тоже делал, безрезультатно
explain

1 simple a index null primary 863 null 42928 using index; using temporary; using filesort;
1 simple b index null primary 863 null 42928 using index;

 

[Wicked]

1) без условия a.ip = b.ip в результат джоина будут попадать пары типа таких:
2 1 1 11:00 3 3 1 13:00
имхо это не есть гуд

2) show create table еще покажи

 

[belbek]

Table Create Table
--------------- --------------------------------------------------------------------------
stat_5_18_05_10 CREATE TABLE `stat_5_18_05_10` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`ip` char(15) NOT NULL DEFAULT '',
`resolution` char(4) NOT NULL DEFAULT '',
`referer` varchar(255) NOT NULL DEFAULT '',
`created_at` int(2) NOT NULL DEFAULT '0',
`time` time NOT NULL DEFAULT '00:00:00',
`c` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`ip`,`referer`,`resolution`,`created_at`,`time`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=52484 DEFAULT CHARSET=utf8

-~{}~ 18.05.10 16:57:

такой большой индекс полностью себя оправдывает в других запросах на агрегирование

 

[Wicked]

еще непонятки в плане правильности запроса...
1) где, собственно, в таблице есть поле url?
2) отсутствие SUM()-агрегации по diff тоже наводит тоску
3) от меня ускользает смысл наличия order by a.created_at asc в запросе при наличии группировки

теперь мысли по быстродействию...
попробуй добавить индекс (`ip`, `id`) и мое условие в on

если не поможет, могу порекомендовать:
1) сделать временную табличку (id, ip, url, created_at, diff)
2) делать в нее insert ... select a.url, a.ip, a.created_at, round( time_to_sec( timediff( if( b.created_at, b.created_at, a.created_at + 500 ), a.created_at ) )/60 ) as diff from stat as a left join stat as b on a.id = b.id - 1 and a.ip = b.ip
3) создавать индекс (url, ip, created_at)
4) делать запрос select url, ip, summ(diff), created_at from ... group by url, ip order by created_at asc

вообще, с такими типами обработки база обычно хреново справляется - лучше это гонять на всяких map-reduce-оподобных решениях, либо, если память позволяет (100000 * 1кб - не так уж и много), выгребать все на сторону пхп и работать циклами и ассоц. массивами

 

[prolis]

Автор оригинала: belbek
PRIMARY KEY (`id`,`ip`,`referer`,`resolution`,`created_at`,`time`,`c`)
такой большой индекс полностью себя оправдывает в других запросах на агрегирование

-все поля постоянно обновляемой таблицы в праймэри кей, Боже! сделай мне разувидеть это