Как оптимизировать 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), и затем насчитайте их для предложения вычисленного СЧЕТА значения. Затем я хочу к ORDER BY, который вычислил столбец SCORE.
Например, принятие взвешивания для PARAM1.5, взвешивание для PARAM2.23, и взвешивание для PARAM3.76, Вы закончили бы с чем-то подобным:
SELECT ID, ((PARAM1 * .5) + (PARAM2 * .23) + (PARAM3 * .76)) / 3 AS SCORE
ORDER BY SCORE DESC LIMIT 10
С некоторой надлежащей индексацией это быстро для основных запросов, но я не могу выяснить хороший способ ускорить вышеупомянутый запрос на такой большой таблице.
Подробнее:
- Каждое значение PARAM между 0 и 1
- Каждый вес относился к PARAMS, между 0 и 1 с
Править--
Упрощенная версия проблемы следует.
Это работает за разумное количество времени:
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 с вычислениями value3 заранее?
2 ответа
Я обнаружил 2 (вроде очевидных) вещи, которые помогли ускорить этот запрос до удовлетворительного уровня:
Сведите к минимуму количество строк, которые необходимо отсортировать. При использовании индекса в поле «id» и подвыбора, чтобы сначала обрезать количество записей, сортировка файлов в вычисляемом столбце не так уж плоха. То есть:
ВЫБРАТЬ t.value1, (t.value2 * an_arbitrary_float) как SCORE FROM (SELECT * FROM sometable WHERE id = 1) AS t ЗАКАЗАТЬ ПО УБЫТКУ ПО ОЦЕНКЕПопробуйте увеличить sort_buffer_size в my.conf, чтобы ускорить сортировку файлов.
MySQL не хватает многих интересных функций, которые могли бы помочь вам в этом. Возможно, вы могли бы добавить столбец с рассчитанным рейтингом, проиндексировать его и написать пару триггеров, чтобы он постоянно обновлялся.