by Romul То, что интересно мне

SQL. Извращение над группировкой

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

Вариант 1.

SELECT id AS rubric_id, (
SELECT id
FROM `forum_topic`
WHERE rubric_id = `forum_rubric`.id
ORDER BY updated DESC
LIMIT 1
) AS last_topic_id
FROM `forum_rubric` ;


Структуры таблиц.

CREATE TABLE `forum_rubric` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID рубрики форума',
`status_id` tinyint(3) unsigned NOT NULL COMMENT 'ID статуса',
`name` varchar(255) DEFAULT NULL COMMENT 'Название',
PRIMARY KEY (`id`)
) ;

CREATE TABLE `forum_topic` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID темы',
`status_id` tinyint(3) unsigned NOT NULL COMMENT 'ID статуса',
`rubric_id` int(10) unsigned NOT NULL COMMENT 'ID рубрики',
`title` varchar(255) NOT NULL COMMENT 'Заголовок',
`added` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Создан',
`updated` timestamp NULL COMMENT 'Обновлен',
PRIMARY KEY (`id`),
KEY `rubric_id` (`rubric_id`,`updated`),
) ;


А вот как можно извратиться и сделать одним запросом:

Вариант 2.

SELECT rubric_id, SUBSTRING_INDEX( GROUP_CONCAT( id ORDER BY updated DESC ) , ',', 1 ) AS last_topic_id
FROM `forum_topic`
GROUP BY rubric_id ;


1. группируем по рубрики
2. собираем ID в строку, сортируя в обратном порядке по дате
3. обрезаем до первой запятой

Результате на данных:
- 50 рубрик
- 100.000 тем

Вариант 1:
1.0464 сек, обход ~10.000 записий
Вариант 2:
0.0629 сек, обход всех 100.000 записей

Если в запросе участвует WHERE,
добавим к примеру status_id = 3

Вариант 3.

SELECT id AS rubric_id, (
SELECT id
FROM `forum_topic`
WHERE rubric_id = `forum_rubric`.id AND status_id = 3
ORDER BY updated DESC
LIMIT 1
) AS last_topic_id
FROM `bank_forum_rubric` ;


1.0136 сек, обход ~2.000 записей

если добавить ключ на rubric_id + status_id:
0.2013 сек, обход ~450 записей

Вариант 4.

SELECT rubric_id, SUBSTRING_INDEX( GROUP_CONCAT( id ORDER BY updated DESC ) , ',', 1 ) AS last_topic_id
FROM `forum_topic`
WHERE status_id = 3
GROUP BY rubric_id ;


0.2059 сек, обход ~20.000 записей

Итог.

Запросы варианта 2 и 4 не используют ключа, проходят по всем записям, они выполняется быстрее или так же, как с вложенным подзапросом (в случае доп. условий).