Форум PHP

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

Хм... здесь наверное необходимо будет преобразовать таблицу referer и разбор самого referer-а осуществлять в count.php (за одно это и ускорит работу счётчика - длительное ожидание администратора будет распределено по мгновенным ожиданиям посетителей :) т.е. в referer пойдут сразу ключевые слова, если жалко терять referer, который вообще говоря нужен для нормально работы системы определения ссылающихся страниц, можно в referer ввести дополнительное поле. А вообще-то нет, лучше так, таблицу referer оставить без изменений, но ввести ещё одну таблицу keyword со следующей структурой

id_keyword - первичный ключ name - ключевое слово (одно) id_referer - внешний ключ для таблицы referer

т.е. полученный реферер от поисковой системы расшифровывать, затем фразу разбивать на отдельные слова и помещать каждое слово в отдельную запись таблицы keyword. Это вообще говоря нормализация второго рода и она позволит решить проблему "Иванов Иван Иванович" и "Иван Иванович Иванов", так как для каждого из запроса будет три записи в таблице keyword, которые нужно будет отсортировать и сравнить (пока не придумал как...).

PS В общем я бы двигался в направлении расшифровки ключевых слов в count.php, т.е. переложил бы это на плечи посетителей, так как их много, а администратор один, ему долго ждать когда все эти 30-тысяч записей расшифруются.

Тогда есть предложение усовершенствовать таблицу refferer следующим образом: хранить рефферер не как текст, а как поле типа enum, которое будет составляться на основе талблицы links. При этом известные реффереры мы сразу будем помещать как enum, а новые - как текст в какую-то резервную таблицу, единственная задача которой эти реффереры сохранить до просмотра их администратором и разгрузить скрипт отчета.
При добавлении новой ссылки в links реффереры из временной таблицы будут обрабатываться и сохраняться в таблице refferer как enum. Полный путь мы при такой схеме, к сожалению, теряем (по крайней мере, у известного рефферера), но в текущей версии отчета он и так нигде не отображается.

По поводу ключевых слов мне тоже приходила в голову мысль о "разборке" рефферера. Вот только как производить двойную группировку?
Есть следующая идея: проверять идентичность запросов можно предварительно отсортировав слова внутри каждого запроса. То есть сначала выбираем данные из базы в массив, затем поочередно вызывая данные из этого массива, сортируем их, и заносим в другой массив, предварительно убедившись что он такого элемента не содержит. Не придумал я пока как к этому прикрутить счетчик и, что основное, как сохранить первоначальный вид запроса. Можно запрос сортировать только на период сравнения... но вот производительность этого варианта вызывает сомнения. Хотя, идея хорошая:)

Так а зачем нам referer менять? Пусть он и служит в качестве такой резервной таблицы, ведь мы в нём сохраняем всё, что требуется для работы с реферером, а так просто не будем сохранять запросы от поисковых систем, как не сохраняем рефереры переходов с сайта, для которого собирается статистика, а enum сейчас используется - это можно не добавлять... если есть потребность, просто добавить новые записи в enum таблицы ip, нам бы как раз ключевые слова как похитрее сохранить, да так чтобы они ещё и места мало занимали :)))

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

>Так а зачем нам referer менять?
Это я просто пытаюсь максимально оптимизировать БД. Таблица refferer лишь немного не дотягивает по размерам до таблицы ip, при существенно меньшем количестве записей. Сейчас у меня в базе информация по 3000 посетителей, а что будет дальше? Думаю, надо все таки систему рассчитывать не только на домашние странички, но и на более крупные проекты.

Идея состояла в том, чтобы добавлять значения в поле enum автоматически при добавлении пользователем рефферера. В таблице ip ему не совсем место: во-первых, у нас уже зарезервированы несколько значений (msn, yandex и пр), а во-вторых, при запросе статистики по рефферерам нам совсем не обязательно ворошить таблицу ip с ее сотнями тысяч записей - вполне достаточно в несколько раз меньшей оптимизированной refferer. Но это пока только рац предложение - я бы взялся за переделку, но при условии поддержки с вашей стороны. Сильно отклоняться от общего курса - себе дороже:)
>не больше 3-х скажем
не пойдет. точную статистику не приведу (сервер сейчас висит:), но у меня большая часть запросов 4-6 слов. То есть это сильно зависит от специфики контента.
Я изначально предлагал сохранять в одной ячейке все ключевые слова запроса (вырезав спецсимволы и пр). Разбив на несколько полей мы, имхо, задачу себе не облегчим: нам необходима статистика по запросам, а не по набору ключевых слов. Если запрос состоит из одной смысловой фразы - этот способ подходит (русский язык позволяет по всякому переставлять слова), а если из двух? трех? такую кашу получим! В общем, слова можно отсортировывать только на момент сравнения, а пользователю показывать запрос в первоначальном виде.

Ну вообще-то да согласен...

PS По поводу больших проектов - на SoftTime висит PowerCounter - примерно 62 000 уникальных посетителей за 3 месяца. Таблица ip 90 Мб, referer - 8.5 Мб, поэтому лично я работаю над архивированием информации и хранении развёрнутой информации только за месяц.

Тут не совсем корректное сравнение: у вашего ресурса есть "постоянная клиентура". А, например, на мой приходят в основном в поисках информации. Так что у меня таблица refferer отличается по размеру от ip менее чем в два раза. Вот так-то.

>да так чтобы они ещё и места мало занимали
можно ради интереса посчитать среднюю длину поискового запроса, и сравнить ее с длиной ip адреса, через отношение частоты поисковых реффереров и хитов... что-то мне подсказывает, что оптимизация базы ключевых слов покажется такой фигней по сравнению с таблицей ip, что внимание ей нужно будт уделять в последнюю очередь:)

Переставлять строку запроса поисковика - бессмысленно, для поисковика, запросы: Иванов Иван Иванович и Иван Иванович Иванов - РАЗНЫЕ, соответственно и результаты поиска будут разные, а если вас не интерисуют ваши позиции в поисковых запросах, то зачем вым статистика по поисковым фразам? Ключевые слова тоже не показатель! Можно быть на первой строке поиска по запросу: "Рефераты по разведеню пчел", а по запросу :"Рефераты" - быть в десятой тысячи, и во второй сотне по запросу: "Пчелы", что тогда прикажете считать ключевым словом?

Не совсем так:
Например запросы "Рефераты по разведеню пчел" и "Разведение пчел. Рефераты" на мой взгляд, одинаковые. Определить насколько вы высоко в рейтинге можно по частоте реффереров с данными запросами. Никто не предлагал делать статистики по ключевым словам, статистика должна быть только по запросам. А вот как эти запросы хранить и обрабатывать мы тут и обсуждаем:)

На самом деле $OMEGA прав, эти умные заразы приорететы словам ставят различные и результат будет одинаковым, если только ресурсов мало, а если их много, то выборка будет различной. Попробуйте набрать "PHP форум" и "форум PHP".

в таком случае, задача неприлично упрощается: остается тупо сравнивать две строки. Собственно, и оптимизировать тогда нечего: надо хранить поисковый запрос и имя поисковика.

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

Поговорили, поговорили ди и бросили?:)
Я тут у хот лога еще один отчет присмотрел. Называется "глубина просмотра сайта". Вещь, опять же, полезная. А то сейчас смотрим среднюю температуру по больнице - с ростом посещаемости падает количество просмотренных страниц. Либо навигация дурацкая, либо много случайных людей по ссылкам заходят. Этот отчет должен помочь разобраться.
Итак, идея похожа на "точки входа". Выбираем количество заходов с ip за сутки, переносим в массив, затем следующие сутки и тд. в итоге имеем числовой одномерный массив который можно посчитать стандартными средствами (если ничего не напутал).
Что меня гложет: в какой-то из тем пробегала фраза о временных таблицах. Если я правильно понял принцип действия, то в нашем случае это бы сильно облегчило задачу: все подсчеты можно было бы вести средствами mysql. Работают ли временные таблицы на старых версиях mysql? Можно ли во временную таблицу помещать данные нескольких последовательных запросов? Ну и вообще, чуть подробнее про этот механизм...

Здесь не очень понятно, что под глубиной понимается? Число просматриваемых страниц с одного IP-адреса? Вообще это было бы очень не плохо - сам о таком думал, когда на Рамблере такую штуку увидел.
В MySQL есть две интересные таблицы. Первый тип временные http://www.softtime.ru/forum/read.php?id_forum=1&id_theme=278. Вместо оператора

CREATE TABLE

используется

CREATE TEMPORARY TABLE

Таблица существует только в пределах сессии, т.е. перешли на другую страницу и она исчезла. Удобно, что такую таблицу может видить только тот клиент, который её создал и конфликта имён не возникает. Вычисления производятся быстро - она расположена в памяти. Временные таблицы введены в MySQL 3.23.0.
Другой тип таблицы - HEAP - это таблица данные в которой тоже располагаются в оперативной памяти (структура таблицы на жёстком диске). Эта таблица видна всем клиентам, но при выключении/сбое сервера данные из неё пропадают (так как расположены в оперативной памяти).

У меня как раз 3.23 и есть... пойду развлекаться запросами. Если все получится - напишу модуль для счетчика. А от вас, все-таки хотелось бы услышать мысли об оптимизиции таблиц... ну и по статистике ключевых слов как следствие этой оптимизации...

Блин... Уже сделал и уже работает... а как же трудности?:)

<?   // Заголовок страницы   include "topcounter.php"; $sql="CREATE TEMPORARY TABLE ttt SELECT count(id_ip) AS hits FROM ip              WHERE systems != 'none' AND                   systems != 'robot_yandex' AND                   systems != 'robot_google' AND                   systems != 'robot_rambler' AND                   systems != 'robot_aport' AND                    putdate >= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') - INTERVAL 1 DAY  GROUP BY ip"; $sql2="SELECT hits, count(hits) AS hits2 FROM ttt GROUP BY hits ORDER BY hits";     mysql_query($sql);    $res = mysql_query($sql2);  while($deep = mysql_fetch_array($res)) {  echo $deep['hits']."-".$deep['hits2']."<br>"; }  // Включаем завершение страницы   include "bottomcounter.php"; ?>

Вот этот код выдает статистику глубины посещений за сегодняшний день. Сегодня все это причешу и добавлю цикл вызывающий статистику за предыдущие дни (наверное, на этом этапе появятся грабли:). Ну и выложу;)

Чёрт, а ну нас на хостинге временные таблицы запрещено создавать... в принципе правильно, с ними делов наделать можно... подозреваю, что так у многих. Вот вам и трудности :))) надо подумать как бы обойти, может в HEAP писать...

А в чем грабли? Почему heap - можно а просто временные - нет? Ну и соответственно вопрос: как их создавать?

HEAP одна на всех клиентов, другое дело, администраторов, просматривающих статистику тоже не 200 человек в минуту :))), а временная таблица создаётся для каждого клиента и в оперативной памяти, т.е. при неаккуратном программировании или при намеренном зловредной коде можно положить сервер хост-провайдера только открыванием новых страниц браузера, причём очень быстро.
Хотя HEAP тоже может быть запрещён - ещё не проверял... нужно будет попробовать. Так вот HEAP создаётся одна на всех - это полноценная таблица, только в памяти... по большому счёту нам даже не нужно, чтобы это была таблица типа HEAP так как в принципе не очень она и большая и вычисления не страшниые - можно обычную таблицу создать и чистить её перед каждым запросом (всё равно HEAP тоже чистить придётся).

PS Для того, чтобы создать таблицу HEAP в параметрах таблицы следует указать тип

CREATE TABLE ttt (   // Опеределение таблицы ) TYPE=HEAP

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

А вы его CREATE обнуляете или создаёте а потом в цикле уже не пользуетесь CREATE?

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

Хм... чего-то не очень понял, что имеется ввиду?

Сейчас это выглядит так:

<?   if ($begin==0) $begin=(intval((time()-$date['data'])/3600/24)+1);   for ($i=$begin; $i>$end; $i--)    {   if($begin == 0) $tmp2 = "";    else      {   $tmp2 = " AND putdate >= DATE_FORMAT( NOW( ) , '%Y-%m-%d 23:59:59' ) - INTERVAL ".$i." DAY ";   $tmp1 = " putdate < DATE_FORMAT( NOW( ) , '%Y-%m-%d 23:59:59' ) - INTERVAL ".($i-1)." DAY ";     } $sql="CREATE TEMPORARY TABLE ttt SELECT count(id_ip) AS hits FROM ip              WHERE systems != 'none' AND                   systems != 'robot_yandex' AND                   systems != 'robot_google' AND                   systems != 'robot_rambler' AND                   systems != 'robot_aport' AND                   systems != 'robot_msn' AND                   $tmp1                    $tmp2                    GROUP BY ip";   mysql_query($sql);  } ?>

То есть мы явным образом указываем что результаты запроса надо поместить во временную таблицу. А если запрос на создание таблицы вынести из цикла, то выбранные данные будут в нее помещаться?

А... вы можете использовать

<?php $sql="INSERT INTO ttt SELECT count(id_ip) AS hits FROM ip              WHERE systems != 'none' AND                    systems != 'robot_yandex' AND                    systems != 'robot_google' AND                    systems != 'robot_rambler' AND                    systems != 'robot_aport' AND                    systems != 'robot_msn' AND                    $tmp1                    $tmp2                    GROUP BY ip"; ?>