Оптимизация моей случайной выборки строк

Я читал о проблемах со скоростью, связанных с получением случайной строки через MYSQL и PHP, и задавался вопросом, как можно улучшить мой код для повышения скорости.

У меня есть информация о баннерах в моей базе данных в таблице под названием «баннеры», и я хочу отобразить случайный баннер в зависимости от позиции на странице и добавить +1 к view_count для этого баннера. Мой метод работает, но для загруженного сайта, где это происходит при каждой загрузке страницы, можно ли улучшить скорость? Спасибо

/* Get banners for position 1 then choose a random one to display  */
$banners = mysql_query("SELECT id,title,banner_url,destination FROM ".TBL_BANNERS." WHERE position = '1' AND status = '1'");
$banner_count = mysql_num_rows($banners) - 1;
$rand_offset = mt_rand(0,$banner_count);
$result = mysql_query("SELECT id,title,banner_url,destination FROM ".TBL_BANNERS." LIMIT $rand_offset, 1 "); 

$banner_id = mysql_result($result,0,"id");
$banner_title = mysql_result($result,0,"title");
$banner_url = mysql_result($result,0,"banner_url");
$banner_dest = mysql_result($result,0,"destination");

/* Add view to this banner */
$database->addViewToBanner($banner_id);

Последняя функция использует запрос:

    "UPDATE banners SET view_count = view_count+1 WHERE id = '$banner_id'"

Я также должен сказать, что, вероятно, в таблице «баннеры» не будет более 100 записей одновременно, но в идентификаторе будут дыры. Идентификаторы могут возрасти до 200, но только половина из них все еще будет существовать.


mysql php random banner
person user29660    schedule 22.12.2011    source источник
comment
Извините, что-то не так с ORDER BY rand() LIMIT 1 (может быть, с ним действительно что-то не так, я не шучу, я на самом деле спрашиваю)?   -  person DaveRandom    schedule 22.12.2011
comment
Я читал, что этот способ не совсем случайный, когда у вас есть дыры в ваших таблицах. Например, мой текущий идентификатор, возвращаемый исходным запросом, равен 9, 15 и 16.   -  person user29660    schedule 22.12.2011
comment
@DaveRandom Да - выполнение ORDER BY RAND() требует больших ресурсов, вы заметите это гораздо больше в таблицах с тысячами / миллионами строк. IMO по-прежнему можно использовать в таблицах с относительно небольшим количеством строк. См. titov.net/2005/09/21/ за хорошее объяснение.   -  person Nick    schedule 22.12.2011
comment
RAND() — это вычисляемый столбец, поэтому его нельзя индексировать. ORDER BY эффективен при поддержке индекса, а не иначе.   -  person Amadan    schedule 22.12.2011

Ответы (3)


arrow_upward
1
arrow_downward

Сгенерируйте случайное число в php и просверлите его таким образом в ПК.

SQL будет

SELECT id,title,banner_url,destination 
FROM TBL_BANNERS 
WHERE id = $rand_offset

Если вы пропустите, запустите его снова. Это дает очень эффективный поиск одной строки, что будет лучше, чем использование обработки ограничения/смещения.

person gbn    schedule 22.12.2011
comment
Хорошо, хороший совет. Я использовал mysql_result для получения идентификатора из случайной строки и использовал этот идентификатор во втором запросе. Спасибо - person user29660; 22.12.2011
comment
Если у вас уже есть случайная строка (для получения идентификатора), вам не нужна вторая SELECT для получения остальных данных — вы могли бы получить ее при получении идентификатора. Проблема в том, как получить случайную строку в первую очередь. - person Amadan; 22.12.2011
comment
Но исходный запрос используется, чтобы убедиться, что я выбираю правильные записи, а не только каждую запись в таблице (WHERE position = '1' AND status = '1'). Также некоторые записи удаляются, поэтому я не могу просто выбрать случайную строку. - person user29660; 22.12.2011

arrow_upward
1
arrow_downward

Идеальный способ сделать это, как вы можете прочитать во многих местах в Интернете, например. блог Антона Титова должен выполнить 2 запроса:

SELECT COUNT(*) AS banners FROM quotes

Затем сгенерируйте случайное число на вашем языке программирования (например, PHP использует mt_rand). и подайте его в этот запрос:

SELECT * FROM banners LIMIT $generated_number, 1

ПРИМЕЧАНИЕ: это нехорошо использовать, если ваша таблица имеет небольшое количество строк. Обычно я все еще использую ORDER BY RAND() до тех пор, пока не узнаю, что в таблице будет более 100 строк.

person Nick    schedule 22.12.2011
comment
Так что несколько запросов — это не всегда плохо? Что ж, полная таблица баннеров будет содержать не более 50 записей одновременно, поэтому, возможно, я действительно более эффективно использую решение ORDER BY RAND. - person user29660; 22.12.2011
comment
Не всегда. Часто при работе с IN(), NOT IN() или RAND() лучше выполнить второй запрос. - person Nick; 22.12.2011

arrow_upward
0
arrow_downward

Вместо первого SELECT используйте это:

$banners_count_result = mysql_query("SELECT COUNT(*) AS num_banners FROM ".TBL_BANNERS." WHERE position = '1' AND status = '1'");
$banner_count = mysql_result($banners_count_result, 0, "num_banners");

Если вы не добавляете и не удаляете новые баннеры каждые пару секунд, рассмотрите возможность кэширования этого результата где-нибудь.

person Amadan    schedule 22.12.2011
comment
Конечно, это сделало бы его менее эффективным, потому что этот запрос вернул бы только 1 часть информации (количество строк)? Если я использую это, у меня не будет случайного идентификатора для использования, поэтому второй запрос будет более сложным. - person user29660; 22.12.2011
comment
Этот запрос дает вам общее количество строк. Затем вы генерируете случайное число в PHP между 1 и количеством строк, которые у вас сейчас есть, и во втором запросе вы вытягиваете всю строку, используя конструкцию LIMIT, как и раньше. Вы все равно выполняли два запроса, но ваш первый (выбрать все строки, запросить счет) был ужасно неэффективным. Этот делает то же самое, но очень эффективно (выбирает количество строк, получает одну строку результата). - person Amadan; 22.12.2011
comment
Я бы подумал, что использование PHP для подсчета количества строк будет быстрее, чем использование метода MYSQL, но я могу ошибаться. Даже если я ошибаюсь, это все равно сделает второй запрос менее эффективным, потому что мне придется использовать LIMIT смещения вместо простого предложения WHERE ID =. - person user29660; 22.12.2011
comment
Если вы разрешите PHP вычислять количество строк, это заставит базу данных подготовить результирующий набор из n строк. Если MySQL делает это, он создает набор результатов только из одной строки. Что касается ID, суть в том, откуда вы берете ID, чтобы использовать предложение WHERE ID = ? Это проблема курицы и яйца. Если все ваши ID не являются последовательными и без дыр, то есть вы никогда не удаляли ни одной строки (или вручную не назначали идентификатор), тогда row# == ID; но полагаться на это чрезвычайно хрупко. Кроме того, сначала сделайте запрос, затем оптимизируйте его — и используйте EXPLAIN, не гадайте. - person Amadan; 22.12.2011
comment
Я получаю идентификатор из mysql_result($banners,$rand_offset,id), но я могу использовать его, только если мой исходный запрос имеет строку идентификатора. - person user29660; 22.12.2011
comment
Нет. Смотри. Если вы используете этот метод, это означает, что вы уже вытащили всю чертову таблицу (хорошо, все строки, которые соответствуют вашим критериям для случайной строки), что а) чертовски медленно и б) у вас уже есть все данные , поэтому вам не нужен второй запрос. Это похоже на покупку телефонного справочника, чтобы найти чей-то адрес, чтобы вы могли пойти к нему домой и попросить у него номер телефона. Это уже там, у вас есть все данные в десятифунтовой книге, которую вы носите с собой. Выбор такой: методом в моем посте делаешь два быстрых запроса. По вашему методу вы делаете один огромный. - person Amadan; 22.12.2011