Оптимизировать запрос с GROUP BY по строке на большой таблице?

MySQL. Есть таблица с новостями, много записей — уже около 70 тыс. и будет расти.


Структура такая:

CREATE TABLE IF NOT EXISTS `news` (<br/>
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,<br/>
 `id_section` int(11) NOT NULL,<br/>
 `title` varchar(250) NOT NULL,<br/>
 `description` text,<br/>
 `image` varchar(250) DEFAULT NULL,<br/>
 `url` varchar(250) NOT NULL,<br/>
 `timestamp` int(10) unsigned NOT NULL,<br/>
 `active` tinyint(1) unsigned DEFAULT '1',<br/>
 PRIMARY KEY (`id`),<br/>
 KEY `id_section` (`id_section`),<br/>
 KEY `timestamp` (`timestamp`),<br/>
 KEY `title` (`title`),<br/>
 KEY `active` (`active`),<br/>
 KEY `url` (`url`)<br/>
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=69653 ;



Есть проблема: записи добавляются автоматически таким образом, что одна и та же запись может быть добавлена несколько раз для разных id_section.


Таким образом при получении данных без указания параметра id_section (показать новости из всех разделов) вылазят дублирующиеся записи. Причем, как правило, они идут подряд. Это плохо.


На таблице небольшого размера решением был такой запрос:

SELECT `news`.* FROM `news` WHERE (active = 1) GROUP BY `url` ORDER BY `timestamp` desc LIMIT 10 OFFSET 20



Однако, уже сейчас такой запрос выполняется 4-5 секунд (!!!).
Нужно решение, которое позволит выйти на показатель хотя бы 0.5 сек.

Примечание: без GROUP BY данный запрос выполняется 0.7 сек. При том, что другие запросы из небольших таблиц занимают микросекунды.


Принимаются любые предложение по оптимизации — не только данного запроса. Возможно есть какие-то специальные приемы для решения подобных задач.
  • Вопрос задан
  • 7232 просмотра
Решения вопроса 1
SovGVD
@SovGVD
может для каждой добавляемой новости делать хеш (от урла или всего текста или заголовка) и пихать его в поле, которое указанно как уникальное — тогда дубликаты будет отсеивать сама субд
Ответ написан
Пригласить эксперта
Ответы на вопрос 8
@MiniM
почему не сделать связь многие-ко-многим между таблицами news и sections?
тогда группировка будет не нужна.
Ответ написан
MpaK999
@MpaK999
Буду!
Кстати EXPLAIN, что по вашему запросу выдаёт? Может базу подтюнинговать, ибо 70 000 это же мелочи.
Ответ написан
Я думал над этим. Оказалось довольно сложно.
В таблице большой объем данных. Добавляются новые записи каждые 20 минут. Получается, что каждые 20 минут нам нужно для каждой новой записи пробежать по всему массиву данных и понять — была уже такая новость или нет. Если была — берем ее ID и записываем «для этой новости есть еще и другой раздел».
Я правильно понял идею? Хранить только уникальные новости, а дублирование выносим в промежуточную таблицу. Проблема в ресурсоемком вычислении «дубликатов». Сравнение-то по url (строка переменной длины).

Тут вы ошибаетесь в нескольких моментах:
70 000 статей это не большой объем данных, это очень маленький объем данных. Большой это на 4-5 порядков больше.
Каждые 20 минут сделать SELECT `id` FROM `news` WHERE `url` = $url это менее затратная операция чем для каждого посетителя делать GROUP BY `url`
Строка переменнйо длины — если длина ограничена можно сделать поле varchar и по нему индекс и все будет нормально работать.

без GROUP BY данный запрос выполняется 0.7 сек

а по полю active индекс сделали?
Ответ написан
fozzy
@fozzy
попробуйте группировать так: GROUP BY MD5(url)
и избавитесь от limit'а, например так:
where id > 20 and id < 30
пример выше подходит только для сплошных id (т.е. что бы не было разрывов/пропусков)
Ответ написан
peter23
@peter23
Кэширование запросов (http://habrahabr.ru/blogs/mysql/108418/) может быть временным решением.
Ответ написан
MpaK999
@MpaK999
Буду!
А почему MyISAM, а не выбран InnoDB, чтобы не лочить таблицу и можно было бы сделать partition таблицы например по месяц-год блоку, объем бы текущих данных сократился. Ну и время бы я не timestamp использовал, а например datetime.

Выносите 20 последних записей например в Redis (Memcache, в память, в файл на диске), а во фронте от туда уже и читайте, это мог бы быть и простой сериализованный массив в памяти, array_shift (array_push) и т.п. как со списком.

А дубликаты проверяйте сразу перед добавлением, каждые 20 минут же это не страшно.
Ответ написан
Комментировать
А откуда вообще возникают дубликаты? Автоматическое добавление новостей — имеется ввиду граббинг с других источников, отсюда и дубликаты?
Ответ написан
Ogra
@Ogra
Может попробовать DISTINCT?
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы