MySQL GROUP BY и внутри LIMIT

SELECT xid,count(*) as num FROM *** WHERE *** AND xid IN (1,5,7,11,13,.....100) AND date>*** GROUP BY xid ORDER BY num DESC

Адекватно никак нельзя.

Народ развлекается not-determinated behavior https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ , но полагаться на это не стоит.

Смотря насколько сложный у Вас запрос сам по себе. Просто попробуйте.

И по ходу дела

1) Если id и date одно и то же, то разумнее выбирать не date> а id> , намного разумнее. Тем более мы сильно подозреваем, что date у Вас не фига не под индексом и вряд ли индекс там разумен, при миллионах-то записей.

2) xid in () не так что бы супер запрос, если есть возможность уменьшить там количество переменных, переписать вида cat_xid in () будет лучше.

3) Не очень понятна глобальная задача, но в задачах когда надо за последнее время выбирать данные (date> намекает), иногда есть смысл вести отдельную таблицу где будут данные именно за последнее время. Выборка по ней будет реактивная, просто надо не забывать чистить ее от старых записей.

3а) При очень большой лени даже вариант вида insert into today_table (selec * from alldays_table) и потом выборка по todays_table может оказаться быстрее всех других вариантов.

Раз в день / неделю / месяц удалять (или бэкапить) все записи кроме последних 1000?

Metal_Messiah,

Варианты:

1) Сделайте фейковое поле fake_xid и прописывайте его не у всех строк, а с нужными вам ограничениями. Выборку и группировку делайте по нему.

2) Вариант с циклом на пхп обязательно проверьте. часто много простых запросов работают быстрее, чем один сложный.

3) Для каждого xid сохранять счетчик (количество) связанных с ним строк. А далее для xid с малым значением этого счетчика делать выборку одним запросом, а остальное - циклом на пхп.

Думал это можно как-то хитро сделать одним вложенным запросом, просто чтобы до group был лимит...

Не, today_table не катит. У меня и так 2 таблицы: одна в которую льется огромный поток инфы, вторая в которую копируется только та которую нужно хранить, первая очищается в полночь. Кому-то нужно запрашивать за сутки, кому-то за несколько суток, кому-то за неделю.

Если id и date одно и то же, тут очевидно что лучше по индексу id, просто написал чтобы видно было что он есть.

Почему xid in () не так что бы супер запрос? У меня этих разных xid тысячи, конкретному пользователю нужны выбранные пара сотен а то и десятков.

Глобальная задача вывод статистики количества записей по каждому xid чтобы пользователь мог просмотреть вручную все записи по выбранным xid'ам на следующем шаге (а интересуют его только те где их много, в порядке убывания).

Ладно, вижу пока 2 пути. Либо попробовать цикл на php, это будет повышение нагрузки по всем запросам, но существенное снижение на этих сбойных xid по которым сотни тысяч записей, в среднем надеюсь что нагрузка снизится.

Второй путь делать таблицу today_records с количествами, обновлять их раз в час по всем тысячам xid'ов или даже лучше при добавлении записей, очищать в полночь и ориентировать пользователей исключительно по количеству за текущие сутки, а уже при детальном просмотре выводить то что запросил пользователь...

Дошли руки внести изменения, отписываю результаты:

На тестовой выборке (штук 50 xid по которым количество записей по 5 меньше сотни, по одному на 170 штук, 2 тыс. и 4 тыс, по остальным xid 0 записей) GROUP BY из 1 поста выдает результат за 22 секунды.

Тот же php скрипт, в цикле по xid делает запросы вида

выполняется за 57 - 74 секунды. Видимо, срабатывает кеш потому повторы проходят быстрее. Лажа в том что LIMIT тут не работает т.к. идет по сути одна строка и это закончится тем же что и было изначальной проблемой.

Тот же скрипт с

Далее подумал что будет если вместо WHERE date>*** (поле - не индекс) сначала найти первый id (PRIMARY) который соответствует условию "date>***" и далее заменить в запросе на id>$some_id_value. Время выполнения 70 секунд. Выигрыша нет, потому что тот же проход по таблице в поисках нужного date, которое индексом делать нельзя так как таблица существенно вырастет в размерах.

SELECT xid,count(*) as num FROM *** WHERE *** AND xid IN (1,5,7,11,13,.....100) AND date>*** GROUP BY xid ORDER BY num DESC

Metal_Messiah,

Если сделать индекс на date, то в размерах вырастет не таблица, а индекс таблицы. Вам что, места жалко? Ну вставки будут чуть тормозить, но не критично и можно отключить сброс индексов на диск по каждому чиху.

Возвращаясь ко времени запросов. Какого размера у Вас таблицы БД эти? (без индексов)? А то по времени запроса это или гигантские таблицы или так себе хостинг.

Если Вы сделаете таблицу хотя бы с соответствием id, date хотя бы по суткам (что бы не раздувать ее), то запрос id>* & date>* будет достаточно шустрым, при этом начальный ИД будет выбрать достаточно просто и быстро из этой таблицы.

И уменьшите паранойю. Покажите нормально запросы которые делаете, а не ***, скажите сколько строк в таблице, покажите структуру и т.д.

Поддерживаю. Желательно тут http://sqlfiddle.com/

Попробуйте сделать временную таблицу типа:

create temporary table xids (xid int not null primary key);

потом добавьте туда свои xid:

insert into xids (xid) values (1); и т.д.

и наконец:

SELECT xid, count(id) as num FROM t1

join xids on (xids.xid = t1.xid)

WHERE date>***

GROUP BY xid

ORDER BY num DESC

поля xid, date (возможно еще что-то из условия where) должны быть проиндексированы.

CREATE TEMPORARY TABLE IF NOT EXISTS tablo AS (SELECT xid FROM ***** WHERE xid IN (...) and date > LIMIT ****;

SELECT xid, count(id) as num FROM ***

join tablo on (****.xid = tablo.xid)

WHERE date>

GROUP BY ****.xid

ORDER BY 2 DESC;