Отсебятина Статьи Проекты
Облако тегов
Web CMS CSS htaccess HTML Javascript MySQL Php Безопасность Мониторы Новостная лента Оптимизация Ошибки Разработка сайта Часы Юзабилити оптимизация ошибки

Выборка произвольных (рандомных) записей из базы

Отсебятина от 21 марта 2009 года.    Теги: MySQL Оптимизация


Выборка по случайному варианту из разных категорий

Допустим, есть галерея, на каждой странице которой выводятся 10 альбомов. Стоит задача, выводить случайную фотографию из альбома как его обложку.

Для решения задачи будем использовать ранее созданную базу. Посмотрим, что она конкретно нам скажет по поводу нашей задачи:
mysql> SELECT COUNT(*) FROM `tst` WHERE `cat`=5; +----------+ | COUNT(*) | +----------+ | 10035 | +----------+ 1 row in set (0.02 sec) mysql> SELECT * FROM `tst` WHERE `cat` BETWEEN 1 AND 9 GROUP BY `cat` ORDER BY RAND() LIMIT 10; +----+-----+------------+ | id | cat | string | +----+-----+------------+ | 18 | 2 | запись №18 | | 6 | 9 | запись №6 | | 9 | 7 | запись №9 | | 21 | 6 | запись №21 | | 3 | 4 | запись №3 | | 8 | 1 | запись №8 | | 11 | 5 | запись №11 | | 2 | 8 | запись №2 | | 5 | 3 | запись №5 | +----+-----+------------+ 9 rows in set (0.38 sec)

Опять те же пироги. Попробуем решить задачу другим способом, но здесь вариант, предложенный мной выше не прокатит: может быть так, что id большего, чем заданное псевдослучайное число, не существует в данной категории , тогда совпадений не будет найдено. Да и скорость такого подхода порядка 0.2 с, несмотря на индексы (выбираются все записи категории и по ним уже идет сравнение по id с условием). Если в условие поставить возможность, чтобы id был меньше заданного числа, то будут выбираться всегда первые id категории.

Предлагаю следующий вариант:
$categories = array (1, 2, 3, 4, 5, 6, 7, 8, 9);
//Эти категории берутся из другой таблицы. Например, `albums`и будут зависеть от $_GET["page"]

$start = microtime(true);

$result = $mysqli->query('SELECT MAX(`id`) FROM `tst`')->fetch_array(MYSQLI_NUM);
$rows = $result[0] - 1;

$rand = mt_rand(1, $rows);
// Делаем выборку строки, подходящей под условие
$query = 'SELECT * FROM `tst` WHERE `id` > '.$rand.' LIMIT 1';
$result = array();
$result[] = $mysqli->query($query)->fetch_array(MYSQLI_NUM);
$notin = $result[0][1];

//Делаем запросы в базу, каждый раз исключая категорию ранее полученных результатов:
for ($i = 1, $count = sizeof($categories); $i < $count; $i++) {
$rand = mt_rand(1, $rows);
$query = 'SELECT * FROM `tst` WHERE `cat` NOT IN ('.$notin.') AND `id` > '.$rand.' LIMIT 1';  $result[$i] = $mysqli->query($query)->fetch_array(MYSQLI_NUM);
//Если в данной категории нет id, больше заданного числа, то делаем обратную сортировку  //и меняем условие, чтобы id был меньше заданного числа.  //Если сортировку оставить прежней, то будет выбрана первая запись категории.
if ($result === null) {
  $query = 'SELECT * FROM `tst` WHERE `cat` NOT IN ('.$notin.') AND `id` < '.$rand.' ORDER BY `id` DESC LIMIT 1';
  $result[$i] = $mysqli->query($query)->fetch_array(MYSQLI_NUM);
 }
//Добавляем полученную категорию в список
$notin .= ", ". $result[$i][1];
}

$stop = round((microtime(true) - $start), 4);

echo 'Запросы <br><table><tr><td>Id</td><td>Cat</td><td>String</td></tr>';
foreach ($result as $row)
echo '<tr><td>',$row[0],'</td><td>',$row[1],'</td><td>',$row[2],'</td></tr>';

echo '</table>Запрос выполнен за: ',$stop,' с<br>';

Запросы в цикле — плохой тон. Но здесь они необходимы.
Смысл в том, что выбирается первая строка, подходящая по условию. После каждого запроса, в список условия включается еще одна категория, к которой следующий результат не должен относится. MySQL ищет по ключу id значение, большее, чем заданное, при этом опуская строки, категория которых не подходит:
SELECT * FROM `tst` WHERE `cat` NOT IN (4, 8) AND `id` > 1 +----+-----+-----------+ | id | cat | string | +----+-----+-----------+ | 2 | 8 | запись №2 | // Подходит по условию `id`, но не подходит по категории, смотрим следующий вариант | 3 | 4 | запись №3 | // Подходит по условию `id`, но не подходит по категории, смотрим следующий вариант | 5 | 3 | запись №5 | // Подходит по всем условиям, выбираем +----+-----+-----------+

Благодаря такому подходу, MySQL просмотрит не очень много записей. Скорость зависит от однородности таблицы по категориям. Если бы после `id`=3 было бы еще 300 записей, принадлежащих этой же категории, то были бы проверены все 300 записей. Неоднородность таблицы может также вызвать второй вариант запроса, если для первого не будет результата, а это еще больше займет времени.


Посмотрим, что нам выдаст запрос на нашей таблице:

IdCatString
211775запись №21176
633636запись №63362
577703запись №57769
450318запись №45030
562419запись №56240
297584запись №29757
329642запись №32963
388567запись №38855
672461запись №67245
Запрос выполнен за: 0.0389 с


Тест в 1000 проходов в консоли:
— Общее время выполнения: 21.8965с
— Время одного прохода цикла: 0.0218965c


Думаю, что с сортировкой по категории, можно справится уже на стороне PHP.
Оставить сообщение






Любое копирование должно сопровождаться ссылкой на сайт.
Если вам что-то не понравилось — сообщайте.
Кича Владимир
x
Мне не нравится этот сайт, удалить его