Форум MySQL

Задача такая: подсчитать количество уникальных посетителей данной страницы, идентифицируя посетителя по его IP адресу. При этом уникальным считается посетитель, который или здесь не был вообще, или со времени прихода которого прошло более 5000 секунд (полтора часа).

В настоящее время я эту задачу решаю так.
- определяю IP посетителя и фиксирую астрономическое время его прихода в секундах;
- проверяю, есть ли в папке, относящейся к данной странице, текстовый файл с именем, совпадающим с IP;
- если такой файл есть, считываю его содержимое, которым является время записи этого файла в секундах;
- если такого файла нет, или если время его записи отличается от текущего более, чем на 5000 секунд, то посетитель считается уникальным. В этом случае формируется текстовый файл с именем, совпадающим с IP, содержимым которого является текущее астрономическое время в секундах. И этот новый файл записывается в папку, соответствующую данной странице сайта.

Как эту задачу решить с использованием БД?

Подготовка:
1. Создать таблицу с именем данной страницы.
2. Сформировать в этой таблице три колонки: IP, время в секундах, идентификатор.

Работа:
1. Узнать, есть ли в колонке IP запись, соответствующая IP посетителя. Как это сделать, каким запросом?
2. Если запись есть, то узнать ее идентификатор. Как?
3. Считать данные из колонки "Время" с этим идентификатором. Как?
4. Если записи с нужным IP нет, то на новую строку записать в таблицу IP адрес очередного посетителя, а в колонку Время текущее время в секундах.
5. Если время отличается от текущего больше, чем на 5000 секунд, то в строку Время этого идентификатора записать текущее время в секундах. Как это сделать?

Верно ли я рассуждаю?
Выгодно ли использовать для этого БД?

>Верно ли я рассуждаю?
Нет. Посмотрите таблицы power counter (Вы же его используете, если не путаю) - там все это уже проделано.

Не могли бы Вы пояснить словами, что не так?

===========================================
За рекомендацию опираться на таблицу power counter спасибо - я этого сделать не догадался! Однако это иллюстрация пока что слишком сложна для меня.

1. Создать таблицу в которой хранить номер страницы и путь к ней
id_page name
2. Создать таблицу хитов (где putdate - время, ip - адрес, а id_page - номер страницы)
id ip id_page putdate
А лучше ещё и идентификатор сессии (дополнительное поле session) кладите в базу данных. Тогда задача подсчета уникальных посетителей сведётся к запросу

SELECT COUNT(DISTINCT session) FROM tbl

>Выгодно ли использовать для этого БД?
Это всегда выгодно хотя бы по тому, что СУБД работает на порядок быстрее файловых операций PHP (который мало того что интерпретатор, так ещё один из самых медленных). Кроме того, если вы не виртуоз при работе с блокировками - рано или поздно вы либо файлы побьете, либо информацию потеряете - так как по уму нужно создавать очередь... Т.е. если с файлами корректно и правильно работать - как раз и получается СУБД - зачем её писать на PHP, когда она на C уже написана.

Подготовка
если учесть, что идентификатором у Вас является сам IP, отдельную колонку с ним создавать вроде как незачем.

CREATE TABLE mycounter (   ip char(20),   hittime INT(11),   PRIMARY KEY(ip) );

Работа:

<? $addr =  $_SERVER['REMOTE_ADDR']; $time = time(); $sql = "SELECT * FROM mycounter WHERE ip = '$addr'"; if(($res = mysql_query($sql)) == 0)   //1     exit( "Error in $sql : ". mysql_error() ); $row = mysql_fetch_assoc($res); //3 if(!$row //2     || intval($row['hittime']) +5000 < time()) //3, 5 {     $sql = "REPLACE INTO  mycounter (ip, hittime) VALUES ('$addr', $time)";     if(($res = mysql_query($sql)) == 0)   //4,5         exit( "Error in $sql : ". mysql_error() ); }

в комментариях помечены примерные пункты Вашего плана.

для: Хеопс
для: Trianon

Большое-большое спасибо, это чрезвычайно интересно и очень полезно!

Но (чисто для понимания) хочу спросить: тот алгоритм, который я описал, просто неоптимален, или его с базой вообще нельзя осуществить (т.е. он принципиально неверен)?
Какой элемент в нем неосуществим (что бы в будущем мне не действовать в этом направлении)?

Можно осуществить, почему нельзя.
Написать код, строго соответствующий Вашим пунктам?

Трудно сказать, будет он неоптимальным или неверным.
Вот представьте такую ситуацию.
Два запроса с нового клиента на сервер пришли практически одновременно.
Ну и примерно параллельно выполняются.

Что станет с Вашим алгоритмом на пункте 4?

Это неверное или неоптимальное поведение?
Вы, вероятно, назовете неоптимальным.
Я - неверным.
Cheops, вероятно, скажет, что в скрипте присутствуют корявки.

PS. Это не потому что скрипт Ваш, а не мой.
Мой наверняка тоже неверный.

"PS. Это не потому что скрипт Ваш, а не мой. "
Я это даже в голове не держу...

"Что станет с Вашим алгоритмом на пункте 4?"
Родится ошибочный результат. И это плохо. Но ведь и в Вашем случае всё также?

"Написать код, строго соответствующий Вашим пунктам? "
Да, мне этого бы очень хотелось!

>>"Что станет с Вашим алгоритмом на пункте 4?"
>Родится ошибочный результат. И это плохо. Но ведь и в Вашем случае всё также?

Глядите внимательно. В моем случае двух записей с одним ip создано не будет.


>>"Написать код, строго соответствующий Вашим пунктам? "
>Да, мне этого бы очень хотелось!

Пожалуйста.
Попытался максимально точно следовать пунктам.
Пункт пятый переехал из-за явной ошибки логики. Грех было не переставить.

<?php //Подготовка: //1. Создать таблицу с именем данной страницы. //2. Сформировать в этой таблице три колонки: IP, время в секундах, идентификатор. /* CREATE TABLE mycounter (   id INT(11) NOT NULL DEFAULT 0 AUTO_INCREMENT,   ip char(20),   hittime INT(11),   PRIMARY KEY(id) ); */ //Работа: $addr =  $_SERVER['REMOTE_ADDR']; $time = time(); //1. Узнать, есть ли в колонке IP запись, соответствующая IP посетителя. if(mysql_result(mysql_query("SELECT COUNT(ip) FROM mycounter WHERE ip = '$addr'"), 0)) { //2. Если запись есть, то узнать ее идентификатор.   $id = mysql_result(mysql_query("SELECT id FROM mycounter WHERE ip = '$addr'"), 0); //3. Считать данные из колонки "Время" с этим идентификатором.   $hittime = intval(mysql_result(mysql_query("SELECT hittime FROM mycounter WHERE id = $id"), 0)); //5. Если время отличается от текущего больше, чем на 5000 секунд,   if($hittime +5000 < $time) // то в строку Время этого идентификатора записать текущее время в секундах.      mysql_query("UPDATE mycounter SET ip = '$addr', hittime = $time WHERE id = $id"); //4. Если записи с нужным IP нет, }  else { // то на новую строку записать в таблицу IP адрес очередного посетителя, // а в колонку Время текущее время в секундах.    mysql_query("INSERT INTO mycounter (ip, hittime) VALUES ('$addr', $time)"); }

Спасибо, все это чрезвычайно интересно!

Буду думать и пробовать.

Ну, ОЧЕНЬ интересно! Кодомания...

ip char(20)

Почему для адреса отводится 20 знаков?

Разве шестнадцати недостаточно?

Или в адресе есть какие-нибудь невидимые служебные биты?

IP-адрес -- это число. Хранить его в текстовом поле нецелесообразно.

только это число, которое в тип данных php нормальным образом не укладывается.

А где оно будет фигурировать в PHP-скрипте в качестве числа?

нигде не будет?
Тем более.

Я Вас не понимаю ваще.

Для IPv4 достаточно 15.
И даже меньше, если начать упаковывать.

Просто для лучшего понимания: набросить в поле 3-5 лишних знаков "просто так" - это нормально? На практике гуру так поступают?

мне просто было лень считать все эти циферки. В 20 входило. В 10 - нет.
кроме того, я точно знаю, что в перспективе это не спасет, поскольку адрес IPv6, выглядящий примерно как 2001:471:1f11:251:290:27ff:fee0:2093 туда всяко не полезет.

Вообще же если смотреть на выбор поля для IP с позиции диапазонного поиска, то хранить его придется совершенно не так. Придется его дополнять до полной ширины, при этом захочется наверняка место поэкономить....

Короче, факторов много. Вам же нужен был пример простой и понятный.

С примером - да. За пример спасибо.

Но я сейчас думаю о полях вообще, на разные случаи жизни. Составляю общую таблицу для занесения всей будущей статистики, и думаю над каждым полем, ибо суммарно уже набежало 350 знаков в полях на 9 столбцов. То ли сделать "впритык" по разумным потребностям, то ли набросить в каждое поле несколько знаков на всякий случай.

Как принято у спецов?
Резерв делается? Или каждый разряд лишний тормоз?

неужели все эти столбцы у Вас фиксированной ширины?

В смысле, будут ли они заполнены полностью на всю ширину отведенного поля? В основном будут заполнены, хотя возможны и пропуски.

Или в понятие "фмксированная ширина" вложен другой смысл?

Я задаю поля так:

$query = "CREATE TABLE log         (               id       INT  (11) NOT NULL AUTO_INCREMENT,               time_mks INT  (18),               ip        CHAR (16),             str_vh   CHAR (24),             p_s      CHAR (16),             p_zapros CHAR (200),             rekl_set CHAR (24),             namerekl CHAR (16),             str_prod CHAR (24),               PRIMARY KEY(id)         )";

Возьмем это вот поле p_zapros CHAR (200)
У Вас каждый запрос будет длиной ровно в 200 символов?
И Вас не беспокоит, что в каждой записи таблицы остаток поля будет бесполезно забит пробелами?

"Возьмем это вот поле p_zapros CHAR (200)
У Вас каждый запрос будет длиной ровно в 200 символов?"
Это поле под поисковый запрос. Посмотрел свою статистику - а основном, 30-60 знаков, максимум 180 знаков.

"И Вас не беспокоит, что в каждой записи таблицы остаток поля будет бесполезно забит пробелами?"
Беспокоит. Собственно, это беспокойство и продиктовало вопрос.
Но я же не знаю, как оно на самом деле: то ли пробелы никаких ресурсов не потребляют, то ли для моей таблицы их потребление вообще незначительно, а может огромно?

Загляните при остановленном сервере в файл с данными таблицы (log.myd) и увидите, что ими-то в основном файл и наполнен. Процентов на 70-80 согласно Вашей статистике.
Вывод - для этого поля куда более подходит тип с переменной длиной - VARCHAR (200)
поменяв тип у поля можно опять остановить сервер и снова заглянуть в файл. Картина будет совсем другой.
Да и сам файл похудеет изрядно.

Да, это мудро!
А если поля не хватает, то информацию обрезают с какой стороны? Надеюсь, начало записывается в любом случае?

>А если поля не хватает, то информацию обрезают с какой стороны?

Поскольку длина в VACHAR на распределение пространства не влияет, то делать её нужно такой, чтоб поле помещалось без опасения быть обрезанным.

На вопрос легко получить ответ, поставив эксперимент.

Кстати, в режиме strict mode сервер не даст записать значение, которое не помещается в поле, и вернет ошибку при такой попытке.

И еще раз напомню - стоит в таблице появиться хоть одному полю переменной длины - тип CHAR теряет смысл.

"стоит в таблице появиться хоть одному полю переменной длины - тип CHAR теряет смысл."

То есть, если сейчас у меня

$query = "CREATE TABLE log          (                id       INT  (11) NOT NULL AUTO_INCREMENT,                time_mks INT  (18),                ip        CHAR (16),              str_vh   CHAR (24),              p_s      CHAR (16),              p_zapros VACHAR (200),              rekl_set CHAR (24),              namerekl CHAR (16),              str_prod CHAR (24),                PRIMARY KEY(id)          )";

то правильнее сделать

$query = "CREATE TABLE log          (                id       INT  (11) NOT NULL AUTO_INCREMENT,                time_mks INT  (18),                ip        VACHAR (200),              str_vh   VACHAR (200),              p_s      VACHAR (200),              p_zapros VACHAR (200),              rekl_set VACHAR (200),              namerekl VACHAR (200),              str_prod VACHAR (200),                PRIMARY KEY(id)          )";

Так?

почему двести-то?
Да, на распределение памяти это число не влияет.
Но начни Вы создавать индексы на полях, и настанет кошмар. Зачем Вам в индексном файле элементы в 200 байт размером?
Есть разумный предел исходя из смысла поля - следует ставить его.
Если такого предела нет, поле - хороший кандидат на массивные типы longtext, longblob и т.п.
Само собой, индексы на таких полях не строятся вообще (если не считать индексов полнотекстового поиска)

"Но начни Вы создавать индексы на полях, и настанет кошмар."
Да ту и без индексов... :)))

Вот при такой ситуации

p_zapros VACHAR (200),               rekl_set CHAR (24),

как понимать, что CHAR теряет смысл?

смысл фиксированных полей только в том, чтобы сделать фиксированную длину всей записи в таблице.
Достаточно одного (любого) поля переменной длины, как запись тоже становится переменной длины.

Это я уже понял, а что происходит с CHAR (24)?

Да ничего? просто исполняется обычным образом? И если здесь 24 достаточно и больше не надо, то пусть так и будет CHAR (24)?

Верно или нет?

просто нет никаких причин не заменить его на VARCHAR(24)

И что-то мне подсказывает, что в каких-то случаях MYSQL сам такую процедуру выполняет. Но тут могу и наврать.
Обратную вещь - замену VARCHAR (1) , VARCHAR(2), VARCHAR(3) на соотв. CHAR он делает точно.

"просто нет никаких причин не заменить его на VARCHAR(24)"
Вот этот вопрос меня и мучил.

"И что-то мне подсказывает, что в каких-то случаях MYSQL сам такую процедуру выполняет."

Как всегда, Вы правы!
Из мануала:
В некоторых случаях MySQL без уведомления изменяет определение столбца, заданное командой CREATE TABLE (Это может осуществляться также для команды ALTER TABLE):

Столбец VARCHAR с длиной меньше, чем четыре, преобразуется в столбец CHAR.
Если некоторый столбец в таблице имеет переменную длину, то и вся строка в результате будет переменной длины. Следовательно, если таблица содержит любые столбцы переменной длины (VARCHAR, TEXT или BLOB), то все столбцы CHAR с длиной, превышающей три символа, преобразуются в столбцы VARCHAR.

Владимир55, и всё-таки лучше будет IP хранить в unsigned int, записывать и считывать через INET_ATON() и INET_NTOA() соотвественно. Тогда IPv4-адреса будут занимать в базе всего по 4 байта, а не 15.

Абсолютно верно сказано. Не хватает лишь примера.

По-моему, Вы совсем недавно занимали другую позицию. Дескать, "это число, которое в тип данных php нормальным образом не укладывается". Причём тут PHP я, к сожалению, так и не понял.

Ключевое слово "пример".
Оно же и в теме топика фигурирует.
Потому и.

Да я и так много сказал.

Даже слишком.