group by varchar field: можно ли ускорить?

[young]

group by varchar field: можно ли ускорить?

Есть таблица, фактически - лог веб сервера, интересующие нас поля

`date` date NOT NULL default '0000-00-00',
`referer_url` varchar(255) collate utf8_unicode_ci NOT NULL default '',

Типичный запрос - топ 20 по реферерам за период

mysql> explain SELECT SQL_NO_CACHE COUNT( id ) AS amount, referer_url FROM referer_log WHERE date >= '2007-11-01' AND date < '2007-12-01' GROUP BY referer_url ORDER BY amount DESC LIMIT 20 ;
+----+-------------+-------------+-------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | referer_log | range | date | date | 3 | NULL | 121789 | Using where; Using temporary; Using filesort |
+----+-------------+-------------+-------+---------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

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

это вообще реально?

ну и почти сразу приходит идея вынести referer_url в справочную таблицу, со всеми уникальными значениями, и группировать по ID-ключу, но опять же, не уверен что это поможет

 

[Апельсин]

Сделай состовной индекс на (date, referer_url). Но у тебя все равно будет Using temporary; Using filesort т.к. ты потом еще сортируешь по amount DESC.

 

[young]

Ключи:

PRIMARY KEY (`id`),
KEY `mega` (`date`,`referer_url`)

mysql> explain SELECT SQL_NO_CACHE COUNT( * ) AS amount, referer_url FROM referer_log WHERE date >= '2007-11-01' AND date < '2007-12-01' GROUP BY referer_url;
+----+-------------+-------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+
| 1 | SIMPLE | referer_log | range | mega | mega | 3 | NULL | 110040 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------+
1 row in set (0.00 sec)


Ну и время выполнения тоже желает лучшего

я что-то сделал не так?

 

[Gas]

Ну вообще-то всё так, появился Using index. Если добавить к предыдущему запросу ORDER BY NULL то из запроса уйдёт Using filesort, но скорости врят-ли это добавить, тем более что сортировка всё равно будет нужна.

Если обработать нужно именно такой набор данных - то действительно можно попробовать заменить referer_url на id из справочника или чтоб справочник не создавать, а грубо оценить будет толк или нет (а если коллизий не будет, то вообще так и оставить):
- создать в таблице новое поле `referer_hash` unsigned BIGINT not null;
- заапдейтить `referer_hash`=CONV(LEFT(MD5(`referer_url`),16), 16, 10);
- заменить текущий составной ключ `mega` на (`date`,`referer_hash`).

Если результата не будет - лично я не вижу как ускорить, тем более в запросе присутствует сортировка по вычисляемому значению. Хороши бы сделать новую таблицу с агрегированными данными.

 

[young]

Gas
от я лось

спасибо, данный вариант всех устраивает
как-то я вообще забыл про него

 

[Gas]

young
а напиши плиз результаты по итогу, хотя бы примерные, ну там: "нифига не дало" или "стало лучше на N%".
Интересно жеж

 

[young]

Хмы, только данные по числам стали совсем другие

ищу ошибку

 

[Gas]

Наверное появились коллизии и цифры стали другие.