MySQL запрос, как?

Question

[Алексей Журбицкий]

Имеются данные в таком виде:

+------------+------+-------+------+
| date       | uid  | page  | hit  |
+------------+------+-------+------+
| 2013-03-25 | 1    | main  | 10   |
+------------+------+-------+------+
| 2013-03-25 | 1    | about | 2    |
+------------+------+-------+------+
| 2013-03-25 | 2    | main  | 1    |
+------------+------+-------+------+
| 2013-03-26 | 1    | main  | 3    |
+------------+------+-------+------+

dump

CREATE TABLE page_stat(
    `id` int NOT NULL auto_increment,
    `date` date,
    `uid` int,
    `page` varchar(64),
    `hit` int,
    PRIMARY KEY (`id`)
);

INSERT INTO page_stat (date, uid, page, hit) values
('2013-03-25', 1, 'main', 10),
('2013-03-25', 1, 'about', 2),
('2013-03-25', 2, 'main', 1),
('2013-03-26', 1, 'main', 3);

Т.е. за каждый день ведется учет сколько пользователь посещал тот или иной раздел.


С подсчетом посещений каждого раздела все понятно:

SELECT page, sum(hit) hits FROM page_stat GROUP BY page;

Результат

+-------+------+
| page  | hits |
+-------+------+
| about |    2 |
| main  |   14 |
+-------+------+

Теперь необходимо посчитать количество уникальных пользователей по каждому разделу.

Результат должен быть такой:

+-------+------+
| page  | uniq |
+-------+------+
| about |    1 |
| main  |    2 |
+-------+------+

Максимум что удалось придумать это

SELECT page, count(DISTINCT uid) uniq FROM page_stat GROUP BY page;

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

Насколько я понимаю в таком запросе на каждый page неявно делается
SELECT DISTINCT uid FROM page_stat WHERE page=...


Какие еще есть варианты поручить данные по уникальным пользователям? Допускается модификация структуры хранения данных.

UPD Необходим был индекс (page, uid)

Answer 1

[gaelpa]

Индексов нет? Насколько я понимаю, индекс на (page, uid) должен ускорить ваш запрос.

Comments

[Алексей Журбицкий]

Есть индекс (uid, page)

[Melkij]

Это значит, что нету индекса. Сперва идёт группировка, потом поля в селекте — индекс не подходит для запроса, получается файловая группировка.
Попробуйте на индексе по page создать. Если page не слишком большой — можно попробовать uid добавить сюда же, а если большой — не имеет смысла.

[Алексей Журбицкий]

В group by page около 600 записей. Попробую создать индекс только по page.

[Melkij]

Имел в виду количество символов в строке. Для группировки строки по индексу, индекс надо строить по всему полю, а не по префиксу. Если же строки объёмны — то индекс распухнет и будет неэффективен.

[Алексей Журбицкий]

Melkij строки до 100 символов, есть кириллица (в utf8) до 50 символов. А если дополнительно хранить хеш строки и по нему сделать индекс и группировку?

[Melkij]

Вполне можно.
Думаю, вам и банального crc32 будет достаточно. А тот компактно упаковывается в unsigned int.

[Алексей Журбицкий]

В общем удалось добиться уменьшения времени выполнения запроса с 30 c до 3 секунд. Так как в оригинале используется еще и условие по дате, то индекс вышел (page, date, uid). С crc32 разницы по скорости не заметил.

Answer 2

[Stdit]

Если все запросы к статистике заранее известны, может быть полезна предварительная агрегация путем денормализации на триггерах. К примеру, таблице page_stat назначить триггер after insert, который заносит запись в таблицу page_uid (page, uid), если такая там ещё не имеется. Запрашивать page_uid будет уже полегче, хотя всё равно потребуется пересчёт. Чтобы избежать и его в том числе, можно и на эту таблицу поставить триггер after insert, который в случае появления новой записи инкрементирует счетчик конкретной страницы в третьей таблице page_uniques_count (page, count). Те данные, что уже есть в базе, надо будет перед активацией триггеров соответственно прогнать запросом и занести в служебные таблицы. Ситуация осложняется введением запросов на количество уников за определенный (не произвольный) период времени (например, за день, месяц). Это реально организовать соответствующим образом, добавив в служебные таблицы дату, округленную до нужного интервала. Для каждого такого интервала может потребоваться отдельная таблица-счетчик, но более крупные интервалы можно получить из мелких (например, по месецам из ежедневной) путем классической агрегации через group by, которая в этом случае будет работать значительно быстрее, чем с сырыми данными из page_stat.

Comments

[Алексей Журбицкий]

Похоже что-то подобное нужно будет делать на следующей итерации.