рейтинг записей в mysql таблице
У меня есть таблица MySQL со многими строками. Таблица имеет столбец популярности. Если я сортирую по популярности, я могу получить разряд каждого объекта. Действительно ли возможно получить разряд конкретного объекта, не сортируя всю таблицу?Я так не думаю. Это корректно?
Альтернатива должна была бы создать новый столбец для хранения разряда, отсортировать всю таблицу и затем цикл через все строки и обновить разряд. Это чрезвычайно неэффективно. Существует ли, возможно, способ сделать это в едином запросе?
4 ответа
Невозможно вычислить порядок (то, что вы называете рангом) чего-либо без предварительной сортировки таблицы или сохранения ранга.
Однако, если ваша таблица правильно проиндексирована (индекс по популярности), база данных может легко отсортировать ее, чтобы вы могли получить свой рейтинг. Я бы предложил что-то вроде следующего:
Выбрать все, включая ранг
SET @rank := 0;
SELECT t.*, @rank := @rank + 1
FROM table t
ORDER BY t.popularity;
Чтобы получить элемент с определенным «идентификатором», вы можете просто использовать подзапрос следующим образом:
Выберите один, включая ранг
SET @rank := 0;
SELECT * FROM (
SELECT t.*, @rank := @rank + 1
FROM table t
ORDER BY t.popularity
) t2
WHERE t2.id = 1;
Вы правы в том, что второй подход неэффективен, если столбец ранга обновляется при каждом чтении таблицы. Однако, в зависимости от количества обновлений в базе данных, вы можете рассчитать рейтинг для каждого обновления и сохранить его - это форма кэширования. Затем вы превращаете вычисляемое поле в поле с фиксированным значением.
В этом видео рассказывается о кэшировании в mysql, и хотя оно зависит от рельсов и представляет собой несколько иную форму кэширования, стратегия кэширования очень похожа.
решение hobodave очень хорошее. В качестве альтернативы вы можете добавить отдельный столбец ранга, а затем, когда популярность строки равна UPDATE d, запросить, изменило ли это обновление популярности свой рейтинг относительно строки выше и ниже нее, затем UPDATE затронуты 3 ряда. Вам нужно будет профилировать, чтобы увидеть, какой метод более эффективен.
Если вы используете таблицу InnoDb, то вы можете рассмотреть возможность построения кластерного индекса по столбцу популярности. (только если порядок по популярности является частым запросом). Решение также зависит от того, насколько разнообразен столбец популярности (0 - 3 не очень хорошо).
Вы можете посмотреть эту информацию о кластеризованном индексе, чтобы увидеть, работает ли это в вашем случае: http://msdn.microsoft.com/en-us/library/ms190639.aspx
Это относится к SQL-серверу, но концепция та же самая, также посмотрите на mysql документацию по этому вопросу.