Как вернуть и кол строк выборки и данные из этой же выборки с заданным лимитом?

Question

[LionG]

Нужно получить и кол строк из DATA и сами строки но применив LIMIT N.

SELECT
    COUNT(DATA.id) as count
FROM
(
  ##ресурсоемкая задача с блекджеком и JOIN'ами
) as DATA

Догадываюсь что нужно в последней выборке взять все столбцы и добавить к ним столбец count продублировав результат COUNT для каждой строки DATA, но не могу понять как это лучше сделать... и как бы не заставить случайно БД для каждой строки пересчитывать каждый раз по новому COUNT.

SELECT
  *,
  COUNT(DATA.id) as count
FROM
(
  ##ресурсоемкая задача с блекджеком и JOIN'ами
) as DATA LIMIT 150
##просит добавить GROUP BY для всех колонок DATA, а потом показывает во всех строках в колонке count 1

UPD: Вопрос решен но не полностью. Можно добавить колонку c count с помощью WITH но задача выполняется дважды либо класть результат во времянку что увеличит использование памяти. Возможно решения с одной выборкой и без времянки не существует.

Answer 1

[mletov]

Вынесите "ресурсоемкую задачу" в временную таблицу.

Из нее посчитайте count и положите в скалярную переменную

А потом уже сделайте выборку из времянки, только доп столбцом добавьте скалярную переменную

Comments

[FanatPHP]

Это шутка такая? Очень остроумно. В стиле рутовых команд с лора.
То есть вместо того чтобы прочитать с диска 150 записей, мы читаем ВСЮ выборку, и мало того - ещё и ЗАПИСЫВАЕМ всю это красоту. хорошо если в память

Answer 2

[FanatPHP]

В африке есть такая птица - страус.
В случае опасности она втыкает голову в песок.
Поскольку при этом она перестает видеть хищника, и думает что хищник тоже её теряет из виду.

Все эти попытки и на ёлку влезть и на пони покататься - получить одним запросом и кусок данных, и полное количество - это как раз такое втыкание головы в песок. В наивной надежде что бд там у себя внутри как-то волшебным образом откуда-то прочитает полное количество и отдаст его без напряга. Не отдаст. Там внутри будет все равно два запроса. И хорошо если без временной таблицы.

Для тех, кому ехать, а не шашечки. единственный реальный и осмысленный вариант

SELECT COUNT(*) FROM ## задача с блекджеком и JOIN'ами
SELECT * FROM /* задача с блекджеком и JOIN'ами */ LIMIT 150

а "ресурсоёмкие" запросы надо оптимизировать. чтобы они перестали быть ресурсоёмкими.

Comments

[LionG]

Допустим оптимизировать запрос больше некуда но он все равно ресурсоемкий.

Например задача занимает 10 мин, вы предлагаете пересчитать по новой те же данные что бы сократить их до 150 записей и ждать 20 мин ? Это похоже на шутку, непонятно только кто так пошутил... MYSQL вроде серьезные ребята.

[FanatPHP]

Начались песни народов крайнего сервера.
А допустим запрос исполняется год? чего так мелко-то, всего 10 минут? мечтать так мечтать

Без "допустим" слабо? Без вот этого детского лепета "навру тут с три короба, лишь бы по-человечески не делать, а свои капризки качать".
Ну ты бы отмазку хоть придумал поумнее тогда, а не "если запрос с джойнами, то ресурсоемкий". Она ж твою "ресурсоемкость" с головой выдаёт

Ну хорошо, пусть запрос у тебя исполняется 10 минут.
Ну расскажи мне, зачем тебе в таком случае общее количество.

[LionG]

FanatPHP, Это байт такой ? А чего уж там год, давай лучше во все тяжкие - N ? Какая разница 0.02 сек или год или N если логически можно сократить в двое.

Без "воды" по делу слабо ?

В тз сказано - нужно кол совпадений по условию и N записей из выборки этим условием (+ сортировка по колонке). А с 1 запросом проще работать чем с 2 при том что у них общие части.

[FanatPHP]

Без "воды" по делу слабо ?

А ты ответ-то читал, родной?

[LionG]

FanatPHP, Да

[FanatPHP]

И где там вода?
По сути у тебя два разных запроса.
Вот их надо просто выполнить, и не строить сложных теорий.

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

[LionG]

FanatPHP, Про страуса очень познавательно.

Так и сделал но вариантом с "WITH" сократив кол обращений к базе и дублирование тела запроса.

Здесь ты ничего не сократишь.

Странно конечно тк по логике можно было бы переиспользовать результат первой выборки. Мб мы оба чего то не знаем... и кто нибудь прольет свет. Хотя я еще не до конца разобрался с WITH, но он скорее всего просто хранит часть запроса и в итоге выборка происходит дважды.

[FanatPHP]

Нельзя переиспользовать результат первой выборки.
чтобы переиспользовать результат первой выборки, надо выбрать ВСЕ записи, которые подходят под условие.
Это то что написали в первом ответе

На игрушечной базе проканает, точно так же как нубское select * from table с последующим num_rows
Но на нормальной за такие выборки будут бить канделябрами

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

[LionG]

FanatPHP, EXPLAIN знаю но WITH не проверял. А зачем и как он все строки таблицы берет ? (отвечать не обязательно) Завтра буду разбираться...

[FanatPHP]

ну а как он по-твоему count посчитает, без всех строк, закачаных во временную таблицу-то?

[LionG]

FanatPHP, В одном из подзапросов был косяк с JOIN'ами поэтому запрос всю исходную таблицу проходил [fixed].

А с EXPLAIN WITH вы имели ввиду что WITH не переиспользует выборку или что то другое ?

[FanatPHP]

Ну я не настолько знаю механику с with, но там явно речь идёт о временной таблице.
проще посмотреть. ну или сюда скинуть

[LionG]

FanatPHP,
Это запрос с WITH (строки + колонка с кол)

[FanatPHP]

Ну вот даже по эксплейну тут видно что ДВА запроса - два раза по три одинаковые строки.

[LionG]

FanatPHP, FOUND_ROWS ?

Answer 3

[Rsa97]

Для MySQL 8

WITH `tmp` AS (
  ##ресурсоемкая задача
)
SELECT `tmp`.*, `s`.`count`
  FROM `tmp`
  JOIN (
    SELECT COUNT(*)
      FROM `tmp`
  ) AS `s`
LIMIT 150

Comments

[LionG]

Странно... Версия сервера: 8.0.22 но пишет "Неизвестный оператор. (near "WITH" at position 0)".
Никакой пример запустить не могу, даже WITH `tmp` AS (SELECT * FROM 'my_table').

[Rsa97]

Лентюй, Что-то умеет. Например, может материализовать CTE во временную таблицу с автоматическим назначением необходимых индексов.
https://dev.mysql.com/doc/refman/8.0/en/derived-ta...
Но, само собой, надо смотреть EXPLAIN на конкретной базе с конкретным запросом.

[Rsa97]

LionG, А это точно MySQL пишет, а не клиент? Вроде сообщение об ошибке самого MySQL выглядит несколько иначе.

[LionG]

Rsa97, Да вы правы это клиент. Сервер пишет:

[2021-03-20 19:02:35] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[Rsa97]

LionG, MySQL в ошибке указывает часть строки сразу после места, где возникла ошибка. Это сообщение больше похоже на непарные скобки или кавычки.

[LionG]

Rsa97, Разобрался... просто "WITH `tmp` AS (SELECT * FROM 'my_table')" не сработает и если в какие либо скобки ставить тоже не работает, нужно что бы сразу шел запрос например SELECT.

Answer 4

[Emil Revencu]

После вашего запроса вызовите это:
select FOUND_ROWS();

Comments

[LionG]

SELECT * FROM `my_table` WHERE foo="bar" LIMIT 10;
SELECT FOUND_ROWS() as count;

Можете подсказать почему FOUND_ROWS() всегда возвращает 1 ? Версия сервера последняя (8.0.23 - MySQL Community Server), в MariaDB также. Данные точно есть)) пробовал и через phpMyAdmin и через консоль напрямую вбивать.

[FanatPHP]

LionG, во-первых, товарищ Emil Revencu слышал звон, да не знает где он. и не понимает как пользоваться этой функцией. поэтому кому-то из вас придется заглядуть в документацию
во-вторых, даже если использовать правильно, то будет все равно медленнее в 100 раз чем нормальный запрос с count
и в-третьих, заглянув в документацию можно узнать что эта функция объявлена deprecated, как раз в силу своей бессмысленности