Как оптимизировать ORDER BY для вычисленного столбца таблицы MASSIVE MySQL

У меня очень большая (80 + миллионная строка) де-нормированная таблица MySQL. Упрощенная схема выглядит следующим образом:

+-----------+-------------+--------------+--------------+
|    ID     |   PARAM1    |   PARAM2     |   PARAM3     |
+-----------+-------------+--------------+--------------+
|    1      |   .04       |    .87       |    .78       |
+-----------+-------------+--------------+--------------+
|    2      |   .12       |    .02       |    .76       |
+-----------+-------------+--------------+--------------+
|    3      |   .24       |    .92       |    .23       |
+-----------+-------------+--------------+--------------+
|    4      |   .65       |    .12       |    .01       |
+-----------+-------------+--------------+--------------+
|    5      |   .98       |    .45       |    .65       |
+-----------+-------------+--------------+--------------+

Я пытаюсь выяснить, есть ли способ оптимизировать запрос, в котором я применяю вес к каждому столбцу PARAM (где вес находится между 0 и 1), а затем усредняет их для вычисления вычисленного значения SCORE. Затем я хочу ЗАКАЗАТЬ, что вычисленный столбец SCORE.

Например, если предположить, что взвешивание для PARAM1 равно .5, взвешивание для PARAM2 равно .23, а взвешивание для PARAM3 - 0,76, вы получите нечто похожее на:

SELECT ID, ((PARAM1 * .5) + (PARAM2 * .23) + (PARAM3 * .76)) / 3 AS SCORE 

ORDER BY SCORE DESC LIMIT 10

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

Подробности:

Каждое значение PARAM находится между 0 и 1
Каждый вес, применяемый к PARAMS, составляет от 0 до 1 с

- EDIT -

Далее следует упрощенная версия проблемы.

Это выполняется в течение разумного промежутка времени:

SELECT value1, value2 
FROM sometable 
WHERE id = 1 
ORDER BY value2

Это не выполняется в течение разумного промежутка времени:

 SELECT value1, (value2 * an_arbitrary_float) as value3 
 FROM sometable 
 WHERE id = 1 
 ORDER BY value3

Используя приведенный выше пример, есть ли какое-либо решение, которое позволяет мне выполнить ORDER BY с вычисленным значением вычисления3 раньше времени?

Ответы

Ответ 1

Я нашел 2 (вид очевидных) вещей, которые помогли ускорить этот запрос до удовлетворительного уровня:

Свести к минимуму количество строк, которые нужно сортировать. Используя индекс в поле "id" и подзаголовок, чтобы сначала обрезать количество записей, сортировка файла в вычисленном столбце не так уж плоха. Т.е.:
```
SELECT t.value1, (t.value2 * an_arbitrary_float) as SCORE
FROM (SELECT * FROM sometable WHERE id = 1) AS t 
ORDER BY SCORE DESC
```
Попробуйте увеличить sort_buffer_size в my.conf, чтобы ускорить эти файловые хранилища.

Ответ 2

Я знаю, что этот вопрос старый, но я недавно столкнулся с этой проблемой, и решение, которое я придумал, заключалось в использовании производной таблицы. В производной таблице создайте свой расчетный столбец. Во внешнем запросе вы можете заказать его. Кажется, он работает значительно быстрее для моей рабочей нагрузки (на порядок).

SELECT value1, value3
FROM (
    SELECT value1, (value2 * an_arbitrary_float) as value3 
    FROM sometable 
    WHERE id = 1 
) AS calculated
ORDER BY value3

Ответ 3

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