Отсебятина Статьи Проекты
Облако тегов
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.
Аноним  16.08.2018 в 09:57
Здравствуй,

Я знаю, что ты занят, и я буду держать это в курсе.

Вы ссылаетесь на http://www.mysql.ru/docs/man/EXPLAIN.html из http://kreker.org/my/18. Недавно я создал углубленную статью о SQL в https://guru99.ru/joins/

Не могли бы вы сослаться на это?

В качестве благодарности я бы поделился вашим сайтом с нашими 20k Social Media Followers, чтобы он стал более привлекательным.

ENGLISH VERSION

Hi,

I know you are busy and so I'll keep this short.

You have linked to http://www.mysql.ru/docs/man/EXPLAIN.html from http://kreker.org/my/18. I have recently created an in-depth article on SQL at https://guru99.ru/joins/

Would you consider linking to it?

As a thank you, I would share your site with our 20k Social Media Followers so it gets more traction.
Аноним  21.08.2018 в 08:02
Здравствуй,

Извините, что снова вторгся в ваш почтовый ящик, но я хочу быстро подключиться, чтобы проверить, что вы получили мой адрес электронной почты ниже?

Мне бы хотелось узнать, что вы думаете. Будет занимать 23 секунды или меньше

====================== Исходное сообщение ===========================

Здравствуй,

Я знаю, что ты занят, и я буду держать это в курсе.

Вы связались с http://www.mysql.ru/docs/man/EXPLAIN.html со страницы http://kreker.org/my/18. Недавно я создал углубленную статью о SQL в https://guru99.ru/joins/
Не могли бы вы сослаться на это?

В качестве благодарности я бы поделился вашим сайтом с нашими 20k Social Media Followers, чтобы он стал более привлекательным.

ENGLISH VERSION

Hi,
Sorry to intrude your mailbox again but I want to quickly reconnect to check that you got my email below?

I'd love to know what you think. Will take 23 seconds or less

=====================Original Message===========================

Hi,

I know you are busy and so I'll keep this short.

You have linked to http://www.mysql.ru/docs/man/EXPLAIN.html from page http://kreker.org/my/18. I have recently created an in-depth article on SQL at https://guru99.ru/joins/
Would you consider linking to it?

As a thank you, I would share your site with our 20k Social Media Followers so it gets more traction.
Аноним  27.08.2018 в 06:00
Здравствуй,

Я добрался до этого и еще не слышал от тебя. Это говорит мне несколько вещей:

1) Вас преследует Т-рекс и не успел ответить.
2) Вы не заинтересованы.
3) Вы заинтересованы, но не успели ответить.

Какой бы он ни был, пожалуйста, дайте мне знать, поскольку я волнуюсь! Пожалуйста, ответьте 1,2 или 3. Я не хочу беспокоиться.

С уважением, Алекс

============================= Исходное сообщение ==========================

Здравствуй,

Я знаю, что ты занят, и я буду держать это в курсе.

Вы ссылаетесь на http://www.mysql.ru/docs/man/EXPLAIN.html из http://kreker.org/my/18. Недавно я создал углубленную статью о SQL в https://guru99.ru/joins/

Не могли бы вы сослаться на это?

В качестве благодарности я бы поделился вашим сайтом с нашими 20k Social Media Followers, чтобы он стал более привлекательным.

ENGLISH VERSION

Hi,

I reached out previously and hadn’t heard back from you yet. This tells me a few things:

1) You're being chased by a T-rex and haven't had time to respond.
2) You aren't interested.
3) You're interested but haven't had a time to respond.

Whichever one it is, please let me know as I am getting worried! Please respond 1,2, or 3. I do not want to be a bother.

Regards, Alex

=============================Original Message==============================

Hi,

I know you are busy and so I'll keep this short.

You have linked to http://www.mysql.ru/docs/man/EXPLAIN.html from http://kreker.org/my/18. I have recently created an in-depth article on SQL at https://guru99.ru/joins/

Would you consider linking to it?

As a thank you, I would share your site with our 20k Social Media Followers so it gets more traction.

Аноним  04.09.2018 в 10:35
Здравствуй,

Я добрался до этого и еще не слышал от тебя. Это говорит мне несколько вещей:

1) Вас преследует Т-рекс и не успел ответить.
2) Вы заинтересованы, но не успели ответить.
3) Вы не заинтересованы. Какой бы он ни был, сообщите нам, когда мы беспокоимся!

Пожалуйста, ответьте 1,2 или 3. Я не хочу беспокоиться.

С Уважением,
Alex


====== оригинальное сообщение =========

Здравствуй,

Я знаю, что ты занят, и я буду держать это в курсе.

У вас есть http://www.mysql.ru/docs/man/EXPLAIN.html на http://kreker.org/my/18. Недавно я создал углубленную статью о SQL в https://guru99.ru/joins/

Не могли бы вы сослаться на это?

В качестве благодарности я бы поделился вашим сайтом с нашими 20k Social Media Followers, чтобы он стал более привлекательным.








English Version

Hi,

I reached out previously and hadn’t heard back from you yet. This tells me a few things:

1) You’re being chased by a T-rex and haven’t had time to respond.
2) You’re interested but haven’t had a time to respond.
3) You aren’t interested. Whichever one it is, please let us know as we’re getting worried!

Please respond 1,2, or 3. I do not want to be a bother.

Regards,
Alex


======original message=========

Hi,

I know you are busy and so I'll keep this short.

You have http://www.mysql.ru/docs/man/EXPLAIN.html to http://kreker.org/my/18. I have recently created an in-depth article on SQL at https://guru99.ru/joins/

Would you consider linking to it?

As a thank you, I would share your site with our 20k Social Media Followers so it gets more traction.
Оставить сообщение






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