Доброго времени суток! Уже более месяца ломаю голову как можно оптимизировать процессы на сайте описанные ниже, может у кого идеи какие возникнут. В общем в базе MySQL есть таблица `offers`, с объявлениями по недвижимости, с характерными полями: площадь, цена, адрес и т.д., а так же контактный телефон. У каждого пользователя который имеет доступ к просмотру объявлений есть черный список телефонов, работающий так - если в объявлении телефон содержащийся у пользователя в черном списке, то это объявление ему не выводится. Все черные списки пользователей хранятся в одной таблице `bl`, в которой есть поле `телефон` и поле `пользователь`. В данный момент, вывод объявлений пользователю происходит так: В php скрипте делается выборка по параметрам из таблицы `offers`, так же делается выборка телефонов из `bl` по нику пользователя, в итоге получаются грубо говоря два массива и сравниваются, получается например 700 объявлений, пользователю выводится только 100 (т.к. на странице 100 объявлений), соответственно делается очень много лишней работы, в данном случае как минимум в 7 раз можно было бы сократить нагрузку...но как? Задавал LIMIT 0, 100 при выборке из `offers`, но в этом случае, т.к. черный список отфильтровывает часть объявлений, то на странице выводится уже меньше 100 объявлений. Есть ли какие идеи? Тут пытался данный процесс перенести полностью на MySQL, но не получилось - viewtopic.php?f=20&t=50680
А зачем делать сравнение пыхом вообще, если это задача выборки? Код (Text): SELECT t1.* FROM offers as t1 LEFT JOIN bl as t2 ON t1.phone = t2.phone WHERE t2.user Is Null
Пробовал так сделать, вот ссылка на тему где это делал - viewtopic.php?f=20&t=50680 Так получается гораздо больше нагрузки, т.к. пользователей тысячи, записей в таблице `bl` миллионы, и получается что тут мы анализируем миллионы телефонов всех пользователей, и запрос в итоге выполняется пол минуты, и это с LIMIT 0, 10 на очень мощном серваке =( Просто в данный момент, в пхп анализируется не вся таблица `bl`, а лишь записи одного пользователя, в среднем это 200 записей.
Тогда что мешает сделать так: Код (Text): SELECT t1.* FROM offers as t1 LEFT JOIN (SELECT user, phone from bl WHERE user = 111) AS t2 ON t1.phone = t2.phone WHERE t2.user Is Null или Код (Text): SELECT t1.* FROM offers as t1 LEFT JOIN bl t2 ON t1.phone = t2.phone AND t2.user = 111 WHERE t2.user Is Null ? Опять же попробуйте сделать индексами поля-внешние ключи для объединения и сравните время выполнения запроса.
Zuldek спасибо, всё работает и быстро )) но в процессе еще возник вопрос с организацей пагинации: Вот допустим выборка в общем выдает 700 записей, на странице же (на сайте) отображается по 100 записей, то есть в запросе используется LIMIT, но нужно же как то узнать что записей 700, и что нужно отобразить 7 страниц, не делать же отдельный запрос COUNT(*), должен же быть еще какой то способ сделать всё одним запросом... Например когда в PHPMyAdmin исполняешь этот запрос без LIMIT, то он выдает - отображено столько то записей из СТОЛЬКИ ТО...
PMA обманывает вас строк выводится на страницу столько, сколько у вас в инпуте указано. по умолчанию 30, кажется. откуда берется "из стольки-то"? если смотреть просто таблицу без хитрых запросов, то он берет число строк из SHOW TABLE STATUS (что для InnoDB дает приблизительное число строк). для запросов с джойнами и условиями он покажет вам, грубо говоря, mysql_row_count() — то есть сколько строк считано в буфер. если есть LIMIT, то число заведомо будет <= limit а на страницу из них выдаст те же 30. то есть PMA не знает сколько у вас строк всего! можете поиграть с трюком SQL_CALC_FOUND_ROWS, чисто для расширения кругозора.
Если в ваших таблицах созданы индексы на внешние ключи по которым идёт объединение, и/или объединения вообще нет и на полях под where или order у вас также созданы индексы, то отдельный запрос с count(*) может быть намного быстрее выборки с SQL_CALC_FOUND_ROWS. Это нужно всегда иметь ввиду и проверять оба варианта если нет уверенности в оптимальности выбора. Указанный недостаток в разных версиях позиционировали в качестве бага и есть мнение что он был пофикшен в свежих версиях мускула(http://bugs.mysql.com/bug.php?id=18454), тем не менее рекомендую прибегать к вышеуказанному совету (upd. под вышеуказанным советом подразумеваю тестирование обоих вариантов с отдельным запросом и без отдельного запроса для определения количества записей).
это не то чтобы совет, просто информирую что специально для указанной задачи разработчиками было придумано такое решение. а посоветовать я могу вот что: а) использовать таки count(*) но, перед этим упростить запрос донельзя. нам нужно знать только число строк, поэтому убираем из запроса всё, что не влияет на число строк и уже упрощенную версию вызываем с count(*). конкретно в этой теме мне лениво искать что и как, пусть автор ищет. б) вообще избавиться от зависимости. пусть будет перемещение Вперед и Назад, но нигде не будет указано сколько страниц всего. такие примеры существуют. сайты с infinite scroll не обязаны знать сколько записей всего. они просто готовы к тому, что запрос вернет строк меньше, чем планировалось — это означет последнюю порцию, конец списка.