Как ускорить выполнение GROUP BY?

Question

[Anton B]

Всем привет!

Есть 700 таблиц по 5М записей, каждая таблица весит по 200Мб.
Структура таблиц следующая:

CREATE TABLE `table` (
  `id` int(11),
  `id2` int(11),
  `id3` int(11),  
  `p1` int(11),
  `p2` int(11),
  KEY `id` (`id`)
) ENGINE=MyISAM;

К каждой из этих таблиц применяется следующий запрос:

SELECT `id1`, `id2`, `id3`, `p1`, COUNT(`p1`) AS `count`, `p2` 
FROM `table` 
GROUP BY `id1`, `id3`, `p1`, `p2`

Результат запроса отправляется INSERT'ом в другую специальную таблицу.

Проблема заключается во времени исполнения запроса, пример 200 сек. Таким образом на обработку всех 700 таблиц мне понадобиться 38 часов. Хотелось бы сократить это время до 1-3 часов.

Вопрос: есть ли способы уменьшить время запроса?

Предпринятые меры:
1. Чтобы трафик результатов запросов не гонять между серверами, php-скрипт разместил на сервере БД.

ЗАДАЧА РЕШЕНА
Всем спасибо за участие, воспользовался функцией GROUP_CONCAT(), что уменьшило время выборки 4 секунд.

Answer 1

[Евгений]

Индексов нет?! Покажите explain запроса.

Answer 2

[Вячеслав Успенский]

Такой запрос к таблице применяться не может, у вас в селекте есть поля не участвующие в группировки и без применения агрегатных функций.
Покажите explain запроса и show create таблицы.

Судя по времени выполнения отсутствуют или неправильно расставлены индексы. Таблицы без шардирования?

ЗЫ Если не хотите публично светить данными то можно и лично, но уже в разрезе оплачиваемой работы.

Comments

[Immortal_pony]

Такой запрос к таблице применяться не может, у вас в селекте есть поля не участвующие в группировки и без применения агрегатных функций.

Mysql игнорирует такие мелочи.

[Вячеслав Успенский]

Immortal_pony: офигеть, тото у меня странное чувство последние полгода не покидает что раньше я менее строгие группировки писал. А можно узнать что но с этими полями делаем? 8) min, max, avg, group by? O_O

[Вячеслав Успенский]

Immortal_pony: кстати видимо два поля с одинаковыми именами он тоже за мелочь считает...

[Immortal_pony]

А можно узнать что но с этими полями делаем? 8) min, max, avg, group by? O_O

Не понял вопрос. Уточни пожалуйста.

кстати видимо два поля с одинаковыми именами он тоже за мелочь считает.

В одной таблице не может быть двух столбцов с одинаковыми именами.
В одной выборке может быть несколько столбцов с одинаковым именем, если в выборке не используются агрегирующие функции.

Пример:

SELCT `user`, `user_id` AS 'user' FROM `table`;  ## Запрос выполнится
SELCT `user`, `user_id` AS 'user'; FROM `table` GROUP BY `user` ## Ошибка "Column 'user' in group statement is ambiguous"

[Вячеслав Успенский]

Immortal_pony: да уже проверил по 1му пункту, MySQL просто молча добавляет поля в group by видимо.
Касательно 2го пункта тс поправил таки запрос

[Алексей Алексеев]

Вячеслав Успенский, Как с вами лично связаться, вы предлагали помощь по оптимизации запросов?

[Вячеслав Успенский]

Алексей Алексеев, было сейчас занимаюсь эпизодически, можно в личку на хабре

Answer 3

[Армянское Радио]

Посмотрите план запроса, добавьте недостающие индексы.

Comments

[Anton B]

Да хоть по всем полям индексы добавлю, ничего не изменится. В любом случае сканируется вся таблица.

[Армянское Радио]

Anton B: индекс существенно ускоряет сортировку и поиск - основные подзадачи группировки.

[FanatPHP]

Anton B: ну молодец, гоняй по 40 часов дальше

Answer 4

[Дмитрий]

Неужели структура данных не позволяет все эти 700 таблиц свести в одну, добавив несколько полей при надобности с доп. критериями? Это же просто ужас, так проектировать БД. Да и вдруг понадобится обновить структуру данных (читай "таблиц") и тогда надо в каждой из 700 таблиц добавить / удалить столбец?

Comments

[Anton B]

Да, структура данных не позволяет свести все 700 таблиц в одну таблицу с 3.5 миллиардами строк и весом 140Гб.

[Евгений]

Может это что-то типа шардинга, но если это шардинг, то почему запросы не делаются параллельно...

Answer 5

[Максим Барулин]

Попробуйте запустить mysqltuner.pl и посмотреть его рекомендации по настройке mysql. Индекс пробовали по всем полям (`id1`, `id3`, `p1`, `p2`)?

Answer 6

[Сергей Сенкевич]

Не могли бы вы словами объяснить, что должен сделать запрос? Тот запрос, что вы написали, не имеет смысла.
Если нужно получить количество уникальных сочетаний (id1, id2, id3, p2), то попробуйте такой запрос:

SELECT `id1`, `id2`, `id3`, COUNT(*) AS `count`, `p2` 
FROM `table` 
GROUP BY `id1`, `id2`, `id3`, `p2`
ORDER BY NULL

Если p2 объявлен как NOT NULL, то должно работать достаточно быстро.