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