Подсчёт уникальных элементов sql vs php

Dreammaker, спасибо, сейчас изменил запись ip со строки на INET_NTOA(...), накоплю количство элементов, посмотрю что там со скоростью.

SELECT COUNT(*) AS _count, user_ip FROM my_table GROUP BY user_ip ORDER BY _count DESC

Можно такой вариант попробовать, если правильно задачу понял.

Только что ради любопытства посмотрел - выборка из 100К записей дистинктом по одному неиндексированному varchar'у (16К уникальных значений) заняла всего 0,7 секунд. Как ни крути, для сервера БД выборки с группировкой по нескольким сотням тысяч записей - никак не феерическая задачка... Что-то не то у вас с настройками сервера, имхо.

Ждал пока накопится вчерашнее количество, чтобы сравнить скорость. После изменение формата записи айпишников в базу, получилось 3 минуты, т.е. как средствами php, отлично, резерв есть. Как записать в виде 4 байт не понял, если конвертить через chr - получается абракадабра (некоторые символы непечатные, не знаю как отнесётся к этому varchar). Записываю в виде INET_NTOA('x.x.x.x') в поле unsigned int. В принципе выигрыш значительный, solved.

seraphim, если 0.7 сек это один запрос - это очень очень много, речь про время работы скрипта на обработку такого запроса 15-20 тысяч раз.

nocomments,

1) Вы бы показали как Вы на php уникальные элементы считаете. Почему-то есть гнусное подозрение, что Вы считаете время именно подсчета, но не учитываете время самой выборки из базы или типа того.

2) В php для преобразования ИП в целое и обратно long2ip и ip2long .

3) Кроме distinct есть смысл попробовать count(*), ip и и группировку

4) Если используете INET_NTOA в запросе, то ни в коем случае не делайте нечто вроде distinct(INET_NTOA()) , иначе Вы будете преобразовывать кучу лишнего.

5) Если это логи, то пересмотрите способ хранения нужных Вам данных. При нескольких миллионах строк Вас уже на любой БД не порадует скорость выборки.

а зачем?

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

Ну это чахленький VPS на котором 4 сайта крутятся, так что логично небыстро. Я как-то сразу не понял, что вы имели в виду когда написали о 20К урлов и 150К записей. Теперь понял :) Тогда присоединяюсь к последнему вопросу.

Сначала записывал в базу айпишники как есть, в виде строки. На php считал простым перебором: доставал айпишник, сравнивал через strstr присутствует ли он в контрольной строке, если нет - добавлял его туда и увеличивал счётчик, если присутствует - дальше.

Время работы скрипта замеряю на одинаковом размере таблицы с айпишниками, 150 000 строк. в целом задача придумалась такая: нужно прописать некоторым страницам сайта количество уникальных просмотров. При этом предусмотреть рост посещаемости, как постепенный, так и скачкообразный (на прошлой неделе в какойто день к обеду было уже 45 000 человек и 800 000 просмотров).

Есть список чуть меньше 20К страниц, для которых нужно посчитать уников за определённый период. На входе есть этот урл, виде срс32, он же есть в таблице с просмотрами, в которой в худшем случае 150 тысяч строк, в лучшем (для кошелька) - 800-1000 К. Берём каждый урл из 20 тыс, находим в таблице, куда записываются просмотры и считаем количество уникальных элементов в такой выборке. Урлы, которые нужно искать лежат в другой таблице (извлекаются туда предыдущей операцией з большой таблице с просмотрами, занимает пару секунд, перебор-инсерт с условиями). Играюсь по всякому.

Такой метод требует в 300-500 раз дольше времени.

INET_NTOA делает тоже самое, только уже в базе. Эта функция применяется ещё при изначальной записи статистики в базу. На момент обсуждаемой операции мы имеем уже базу, где грубо говоря два целочисленных столбца - урл и айпи.

Структура примерно такая:

1 таблица: Каждая строка соответствует одному просмотру страницы, в ней два числа, одно - урл, второе - айпи, записанное туда функцией INET_ATON. Контрольная выборка для замера 150К строк.

2 таблица: список урлов где они не повторяются. 20К строк.

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

nocomments добавил 01.12.2010 в 23:24

А тут немного другая ситуёвина, 8 ядер по 1,6Ггц, 8 гиг памяти и всё практически эксклюзивно под этот проект :)

Что-то вроде тривиального апдейта:

UPDATE tbl2 t2, 

(SELECT url, count(ip) uniq_views

FROM tbl1 

GROUP BY url) q

SET t2.page_uniq_views = q.uniq_views

WHERE t2.url=q.url

во второй таблице уникальный ключ - поле с урлом.

запрос как-то так:

replace into table2 (select url, count(1) from (select url, ip from table1 group by url, ip))

ещё можно делать первый запрос во временную таблицу с ключем по url + ip

а оттуда выборку с группировкой уже куда надо.

ну и нужные индексы конечно чтоб были :)

Кстати я б урлы сократил, оставив только их хэши.