Оптимизировать запрос MySQL. Как узнать сколько всего рядов когда использую limit?

Question

[Александр]

$numPosts = 3; //Постов на страницу
$start=abs($page*$numPosts); //Начинать запрос начиная с поста

$STH = $GLOBALS["mysqlcon"]->prepare("SELECT * FROM post WHERE MATCH (tags) AGAINST ('".clearStr($_GET['search'])."' IN BOOLEAN MODE)");
$STH->execute();
$totalPages=ceil($STH->rowCount()/$numPosts); //Узнаем сколько всего страниц существует

 $STH = $GLOBALS["mysqlcon"]->prepare("SELECT * FROM post WHERE MATCH (tags) AGAINST ('".clearStr($_GET['search'])."' IN BOOLEAN MODE) LIMIT $start, $numPosts");
 $STH->execute();

while($row = $STH->fetch()) { ... }

Необходимо послать запрос в бд MySQL с лимитами (использую для страниц), но в тоже время знать сколько всего страниц существует. Сейчас делается запрос 2 раза, но это ведь лишняя работа. Посоветуйте как правильнее и лучше это делать:)

Answer 1

[FanatPHP]

Сам по себе еще один запрос - это не лишняя работа, а ерунда на постном масле.
Вопрос не в том, сколько запросов выполняется, а что именно они делают.

И в данном случае эти два запроса делают ужас, летящий на крыльях ночи. Я сейчас расскажу, что делает ваш код.
Сначала этот код просит базу данных отправить в РНР все найденные запросом строки. Все. Без лимита. Чтобы РНР мог посчитать их и потом выбросить.
После этого код еще раз просит базу отправить, только не все строки, а только часть. И теперь РНР уже не выбрасывает полученные строки, а честно выводит.
Понятное дело, что смысла в этих замысловатых телодвижениях - ноль целых, ноль десятых.

Как правильно получить общее количество найденных строк? Надо попросить базу посчитать их

Ну и разумеется, использование prepare вместе с доморощенной и небезопасной функцией "clearStr" - это вообще за гранью добра и зла. Это примерно как хранить миллион в сейфе, но не запирать его на ключ, а подпирать дощечкой.

$GLOBALS["mysqlcon"] - это отдельный ужас, но на фоне всего остального меркнет.

Правильный код будет таким

$sql = "SELECT count(*) FROM post WHERE MATCH (tags) AGAINST (? IN BOOLEAN MODE)";
$STH = $GLOBALS["mysqlcon"]->prepare($sql);
$STH->execute([$_GET['search']]);
$totalPages=ceil($STH->fetchColumn()/$numPosts); //Узнаем сколько всего страниц существует

$sql = "SELECT * FROM post WHERE MATCH (tags) AGAINST (? IN BOOLEAN MODE) LIMIT ?,?";
$STH = $GLOBALS["mysqlcon"]->prepare($sql);
$STH->execute([$_GET['search'],$start, $numPosts]);

Поскольку все советуют синглтон, вот готовый пример: https://phpdelusions.net/pdo/pdo_wrapper#singleton
С ним, кроме прочего, код будет короче

$sql = "SELECT count(*) FROM post WHERE MATCH (tags) AGAINST (? IN BOOLEAN MODE)";
$numRows = DB::run($sql, [$_GET['search']])->fetchColumn();
$totalPages=ceil($numRows/$numPosts); //Узнаем сколько всего страниц существует

$sql = "SELECT * FROM post WHERE MATCH (tags) AGAINST (? IN BOOLEAN MODE) LIMIT ?,?";
$STH = DB::run($sql, [$_GET['search'],$start, $numPosts]);

Comments

[Александр]

Спасибо огромное. :) Учту и все переделаю. :)
GLOBALS использую потому что по другому не могу получить доступ к переменной внутри функции.

Answer 2

[Stalker_RED]

FanatPHP все правильно написал, но если стоит задача поменьше нагружать БД, то можно еще круче.

1. Делаете выборку с лимитом, указав при этом опцию SQL_CALC_FOUND_ROWS.
2. Сразу после этого запрашиваете FOUND_ROWS(), который вернет общее количество найденный строк (без учета лимита)

Эта циферка уже и так посчитана при предыдущем запросе с выборкой, и FOUND_ROWS() просто вернет значение, без дополнительного обращения к таблицам.

https://dev.mysql.com/doc/refman/8.0/en/informatio...

В принципе это можно и в один запрос собрать, используя UNION, но уменьшится читабельность.

Comments

[FanatPHP]

Все наоборот. запрос с FOUND_ROWS() будет тормозить выборку. Чтобы посчитать SQL_CALC_FOUND_ROWS, база должне будет каждый запрос выполнять так, как будто он сделан с лимитом в максимальное количество записей.

[Stalker_RED]

FanatPHP, не настолько сильно, как отдельный запрос.

[FanatPHP]

Запрос с SQL_CALC_FOUND_ROWS будет быстрее только если сам запрос очень медленный. Но если запрос медленный то его надо оптимизировать, а не костыли ставить. Тем более такие, которые могут уронить сервер, если запрос вернет слишком много строк

[Сергей Щучкин]

SQL_CALC_FOUND_ROWS хотят выпилить уже с 8й версии

Answer 3

[Radjah]

Считай ряды средствами сервера БД. Для количества есть COUNT(поле). И первом запросе оставь какое-нибудь одно поле типа ID или что-то похожее.

Comments

[Александр]

Это ведь не правильно. Я два раза гружу БД с выборкой. Если в БД будет хотябы 10 000 записей, каждый запрос будет очень долго грузиться, а я их два раза отсылаю в добавок.
Вначале отсортировать всю БД чтобы просто узнать сколько же строк всего имеется, а потом еще раз отсортировать БД чтобы показать строки С ... ПО ...
Хотелось бы как-то сгруппировать это одним запросом..

[Radjah]

Ic2d, первый запрос с добавлением count(поле) вместо звездочки тебе вернет результат из одного поля с числом.
100500 строк тебе вернет как раз второй запрос. БД один фиг закэширует данные в память.

Сейчас у тебя весь массив гоняется дважды через сокет. В случае с COUNT у тебя через сокет будет гоняться только кусок данных от запросы с LIMIT.

Answer 4

[ThunderCat]

$GLOBALS это ваще жесть конечно... как и вставка гданных из гет в запрос(даже с какой-то обработкой), у вас же препэйреды по идее должны работать!

$STH = $GLOBALS["mysqlcon"]->prepare("
SELECT count(id) as count
FROM `post` 
WHERE MATCH (tags) 
AGAINST ('".clearStr($_GET['search'])."' 
IN BOOLEAN MODE)");

UPD: вам в любом случае придется делать 2 запроса, так как количество результирующих строк разное, кроме того каунт по первичному ключу будет быстрым, а выборка скорее всего сразу закешируется после первого запроса.

Comments

[Александр]

А как мне еще использовать ? Эти строки находятся внутри функции..Не нашел другого варианта кроме как использовать глобалс :(

[ThunderCat]

Ic2d, даже вариант со статическим методом более гибок и функционален, хранение в глобалс вообще дикий моветон и говорит о нереальной кривизне архитектуры.