Как оптимизировать запрос sql?

Question

[Назар Мокринский]

Есть запрос:

SELECT
	AVG(`d`.`x`),
	SUM(`d`.`y`)
FROM `data_table` AS `d`
INNER JOIN `group_table` AS `g`
	ON `d`.`g_id` = `g`.`id`
WHERE
	`d`.`param`	= 1
GROUP BY `g`.`group_column`

Он работает медленно, по скольку таблица group_table большая (70 тыс), а data_table огромная (3 млн записей), это для теста, реальный размер будет в сотни раз больший.
Таблица group_table введена с целью оптимизации раздела, так как нужны индексы по group_column, которых несколько разных, а данные повторяются, соответственно не хотелось добавлять несколько колонок с индексами для этих миллионов записей.
Но дело в том, что при выборке, когда подходит большое количество записей из data_table запрос выполняется 12-18 секунд с использованием быстрого SSD, что неприемлемо.

Вопрос в том, как изменить запрос, чтобы ускорить выборку (нужна группировка по колонкам типа group_column). Подходят варианты с изменением структуры БД, используется MariaDB, подходят решения, которые работают только в ней без обратной совместимости с MySQL.

Очень надеюсь на коллективный разум.

UPD структура таблиц из примера:

CREATE TABLE IF NOT EXISTS `data_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` int(11) NOT NULL,
  `y` int(11) NOT NULL,
  `param` int(11) NOT NULL,
  `g_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `param` (`param`),
  KEY `g_id` (`g_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `group_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_column` int(11) NOT NULL,
  `group_column2` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `group_column` (`group_column`),
  KEY `group_column2` (`group_column2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

[egor_nullptr]

Структуру таблиц покажите, пожалуйста.

Comments

[Назар Мокринский]

Добавил в вопрос

Answer 2

[egor_nullptr]

Можете попробовать добавить составной ключ из (param, g_id, x, y). Это немного ускорит выполнение запроса. Но при планируемых объёмах вы не сможете получить очень быструю выборку, т.к. агрегирующие функции должны пройтись по каждой записи из результата (и это даст вам Using temporary и Using filesort в explain). Советую вам либо использовать кеширование (заранее один раз подождать долгий запрос, потом N минут использовать результат), либо посмотреть в сторону MongoDB и MapReduce.

Comments

[Назар Мокринский]

С таким объемом данных я бы на кэширование не надеялся, поиск по одному param дан для примера, будет всё сложнее, и почти всё будет мимо кэша.
Об MongoDB уже часа 3 читаю, раньше не использовал, но, похоже, у меня нет выбора.

Answer 3

[svd71_1]

SELECT
    AVG(`d`.`x`),
    SUM(`d`.`y`)
FROM `data_table` AS `d`,  `group_table` AS `g`
WHERE
    `d`.`param`	= 1
    and
    `d`.`g_id` = `g`.`id`
GROUP BY `g`.`group_column`

Comments

[Назар Мокринский]

Каким образом неявный JOIN может быть быстрее за явный?
Не влияет на скорость совершенно никак.

Answer 4

[Леонид Гатальский]

Раз ты в результате, используешь только данные из таблицы data_table, то смысла в join я вообще не вижу

Напиши следующим образом

SELECT
    AVG(d.x),
    SUM(d.y)
FROM data_table AS d
WHERE EXISTS (SELECT 1 FROM group_table AS g WHERE g.id =d.g_id ) AND d.param = 1
GROUP BY g.group_column

Comments

[Назар Мокринский]

А разве g будет доступно вне подзапроса? Я думаю, нет.