MySQL GROUP BY и внутри LIMIT

Metal_Messiah
На сайте с 01.08.2010
Offline
129
1739

Добрый вечер. Есть "тяжелый" запрос

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
Тяжелым он становится когда в таблице оказывается на определенном xid не десяток - сотня записей а сотня тысяч или даже лям, таких xid буквально пара штук, но никто не гарантирует что их число в будущем не вырастет. Как можно в MySQL лимитировать количество найденных записей по каждому значению поля xid, скажем, тысячей последних по индексу id либо по date (что будет одно и то же) чтобы запрос не уходил под max_execution_time? Этот запрос просто выводит частотность, потом уже все остальное делается. Хранить в отдельной таблице эти самые количества дело хорошее, общее число так и хранится и для других целей используется, но where date>... мешает это нормально реализовать. Если к запросу выше дописать LIMIT будут взяты только первая тысяча записей и потом сгруппированы, если же там в 1ю тысячу как раз попадет кусок того ляма, получится в выборке не будет данных по другим xid.

Есть еще вариант прохода на php в цикле по указанным xid и запрашивать where xid='***' LIMIT 1000 для каждого отдельно, но, кажется что это будет существенно дольше чем текучий вариант из-за того что таблица будет просматриваться не 1 раз а N раз где N число этих самых запрашиваемых xid...

anonymous, думай что говоришь и не забывай подписать отзыв :) Газета Сейчас (http://sejchas.pp.ua) - свежие новости на автомате
edogs software
На сайте с 15.12.2005
Offline
734
#1
Metal_Messiah:
Добрый вечер. Есть "тяжелый" запрос
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
Тяжелым он становится когда в таблице оказывается на определенном xid не десяток - сотня записей а сотня тысяч или даже лям, таких xid буквально пара штук, но никто не гарантирует что их число в будущем не вырастет. Как можно в MySQL лимитировать количество найденных записей по каждому значению поля xid, скажем, тысячей последних по индексу id либо по date (что будет одно и то же) чтобы запрос не уходил под max_execution_time? Этот запрос просто выводит частотность, потом уже все остальное делается. Хранить в отдельной таблице эти самые количества дело хорошее, общее число так и хранится и для других целей используется, но where date>... мешает это нормально реализовать. Если к запросу выше дописать LIMIT будут взяты только первая тысяча записей и потом сгруппированы, если же там в 1ю тысячу как раз попадет кусок того ляма, получится в выборке не будет данных по другим xid.

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

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

Metal_Messiah:
Есть еще вариант прохода на php в цикле по указанным xid и запрашивать where xid='***' LIMIT 1000 для каждого отдельно, но, кажется что это будет существенно дольше чем текучий вариант из-за того что таблица будет просматриваться не 1 раз а N раз где N число этих самых запрашиваемых xid...

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

И по ходу дела

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 может оказаться быстрее всех других вариантов.

Разработка крупных и средних проектов. Можно с криптой. Разумные цены. Хорошее качество. Адекватный подход.
S
На сайте с 21.10.2015
Offline
33
#2
Metal_Messiah:
Добрый вечер. Есть "тяжелый" запрос
Как можно в MySQL лимитировать количество найденных записей по каждому значению поля xid, скажем, тысячей последних по индексу id либо по date (что будет одно и то же) чтобы запрос не уходил под max_execution_time?

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

B
На сайте с 13.02.2008
Offline
239
#3

Metal_Messiah,

Варианты:

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

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

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

Metal_Messiah
На сайте с 01.08.2010
Offline
129
#4

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

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

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

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

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

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

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

Metal_Messiah
На сайте с 01.08.2010
Offline
129
#5

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

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

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

SELECT count(*) as num FROM *** WHERE *** AND sid=$id AND date>*** LIMIT 1000"

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

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

SELECT count(*) AS num FROM (SELECT id FROM *** WHERE *** AND xid=$id AND date>*** LIMIT 500) as tmp
после чего делается arsort() сортировка массива количеств (вместо ORDER BY num DESC из 1 поста) - она ничего не стоит по времени так как там около 50 элементов массива - занимало это все 59 секунд, много раз не гонял.

Далее подумал что будет если вместо 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

edogs software
На сайте с 15.12.2005
Offline
734
#6

Metal_Messiah,

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

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

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

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

D
На сайте с 18.12.2015
Offline
147
#7
edogs:
уменьшите паранойю. Покажите нормально запросы которые делаете, а не ***, скажите сколько строк в таблице, покажите структуру и т.д.

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

Разработка и поддержка высоконагруженных проектов.
Оптимизайка
На сайте с 11.03.2012
Offline
396
#8
Metal_Messiah:
Добрый вечер. Есть "тяжелый" запрос

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

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) должны быть проиндексированы.

⭐ BotGuard (https://botguard.net) ⭐ — защита вашего сайта от вредоносных ботов, воровства контента, клонирования, спама и хакерских атак!
C
На сайте с 04.02.2005
Offline
246
#9

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;

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий