Mysql Получение одной строки при групировке

StVolodymyr

Новичок
У меня есть такая таблица
trips.png
по всех полях от country_from до season_id происходит поиск. Он может быть с любой комбинацией полей или вообще без них. По полю mask делаю групировку, и для кажной группы мне нужно получить одну запись с найименьшим factor или price. Сейчас нужный мне результат я получаю таким запросом:
SELECT t1.`trips_key` FROM `travel_site`.`trips_memory` t1
INNER JOIN `travel_site`.`trips_memory` t2 on t1.trips_key=(
SELECT t3.`trips_key` FROM `travel_site`.`trips_memory` t3
where t3.`mask`=t1.`mask` and t3.`city_from`=39 and t3.`country_to`=2 order by t3.factor ASC limit 1 )
WHERE t1.`city_from`=39 and t1.`country_to`=2
GROUP BY t1.`mask`
но выполняеться он совсем медленно

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

Возможно кто-то уже встречался с подобной задачей, как ее решали? Или возможно есть базы даных которые лутше чем MySQL справляются с подобного рода задачами
 

artoodetoo

великий и ужасный
MySQL справляется на ура!

помоему ошибка в логике группировки. зачем JOIN? почему во фразе SELECT стоит это поле?
ладно если тормозит, он у тебя непредсказуемый результат выдает, насколько я вижу
 

StVolodymyr

Новичок
этот запрос работает вполне предсказуемо и возвращает точно тот результат что мне нужно, но он делает это медленно

я взал реальную задачу. так как не хотел придумывать синтетический пример.. но попробую еще на примере:
есть такие даные
id | group_field | sort_field
1 1 3
2 2 2
3 1 1
4 2 1
5 1 2
6 2 3

мне надо получить айдишку минимального sort_field при групировке по полю group_field. тоесть получить такое

id | group_field | sort_field | min_sort_field
3 1 1 1
4 2 1 1

и запрос

SELECT t1.*, t1.sort_field as min_sort_field FROM `test`.`test` t1
INNER JOIN `test`.`test` t2 on t1.id=(
SELECT t3.`id` FROM `test`.`test` t3
where t3.`group_field`=t1.`group_field` order by t3.sort_field ASC limit 1 )
GROUP BY t1.`group_field`
это далает

если сделать просто
SELECT *, min(sort_field) as min_sort_field FROM test.test group by group_field;
получаем
id | group_field | sort_field | min_sort_field
1 1 3 1
2 2 2 1
 

StVolodymyr

Новичок
да, я рассматривал и такой вариант, но поскольку sort_field не уникальное поле, то более правильно во внешнем запросе еще добавить сортировку по group_field

http://sqlfiddle.com/#!2/e2e0a/2/0

я не делал замеры на базе с, но судя по експлейну не уверен что это будет существенный выиграш в производительности.
сейчас у меня база не большая примерно 50 тис записей, но будет расти
 

artoodetoo

великий и ужасный
Ну если неуникально, может быть трюк с переменными выручит. Я вспомню в каком контексте это было и сделаю пример...

Я избегаю таких вещей как SELECT *... GROUP BY fld и тебе советую. MySql допускает такой синтаксис (при дефолтовых настройках), но не факт, что оно будет работать везде!
Это создает неоднозначность в случае если не все поля уникально определяются группирующим выражением. По стандарту во фразе group by должны быть все поля, которые встретились во фразе select и не обернуты при этом в агрегатные функции.

http://mysqlru.com/reference/data-manipulation/select.html
Расширенный оператор GROUP BY в MySQL обеспечивает, в частности, возможность выбора полей, не упомянутых в выражении GROUP BY. Если ваш запрос не приносит ожидаемых результатов, прочтите, пожалуйста, описание GROUP BY.
Гуглить MySQL ANSI_MODE
 
Последнее редактирование:

StVolodymyr

Новичок
прочилал хабр и пробую сделать через переменные

http://sqlfiddle.com/#!2/e2e0a/26

но никак не могу понять почему в запросе не устанавливается переменная @i в 0 при проходе через group_field.. я что-то делаю не так)
 

artoodetoo

великий и ужасный
Если счетчики инициировать во фразе FROM, то запрос реально выполнить из PHP mysqli_query!
http://sqlfiddle.com/#!2/e2e0a/52
Код:
SELECT *
FROM
  (
    SELECT
      `id`,
      (@rn := IF(@prev = `group_field`, @rn + 1, 1)) AS `row_num`,
      (@prev := `group_field`) AS `group_field`,
      `sort_field`
    FROM
      `test` AS t1,
      (SELECT @prev := NULL, @rn := 0) AS vars
    ORDER BY `group_field`, `sort_field`
  ) AS t4
WHERE row_num < 2
Мне не очень нравится этот запрос, потому что

а) приём с row_num < N действительно красив при N > 2.

б) здесь будет полный перебор всех строк исходной таблицы.

Можно было бы вместо `test` AS t1 написать какой-нибудь промежуточный запрос, уменьшающий число строк до количества групп + дубликаты. Оставлю как есть ради читаемости..
 
Последнее редактирование:

StVolodymyr

Новичок
Да это то что надо, это пока лутший вариант из найденых

На моей базе (римерно 44 тис. записей) запрос
Код:
SELECT t1.*, t1.sort_field as min_sort_field FROM `test` t1
INNER JOIN `test` t2 on t1.id=(SELECT t3.`id` FROM `test` t3 where t3.`group_field`=t1.`group_field` order by t3.sort_field ASC limit 1 )
GROUP BY t1.`group_field`
вообще отстойный и выполняется ~40 сек
этот запрос
Код:
SELECT t1.*
FROM test AS t1 INNER JOIN
(
  SELECT group_field, MIN(sort_field) as sort_field
  FROM test
  GROUP BY group_field
) AS t2 ON t1.group_field=t2.group_field AND t1.sort_field=t2.sort_field
group by t1.group_field
уже получше ~0.8 сек.

Запрос с переменными - ~0.14 сек.
Пока для моих объемов пойдет

Но все же непонятно почему не работает в конструкции if (@m=group_field, @i:=@i+1, (@i:=0) or (@m:=group_field)) присвоение @i:=0 хотя @m:=group_field срабатывает
 
Последнее редактирование:

artoodetoo

великий и ужасный
может быть (A or B) выполняется справа налево :) не знаю. замени на "and"
 

StVolodymyr

Новичок
Опять хочу вернуться к этой теме, така как и ожидалось при увеличении количества данных запрос выполняеться уже недопустимо долго
Сейчас размер базы 900 тис записей и уже есть данные для того чтобы увеличить ее до 1.5 млн. На 600 тис запрос выполняеться уже порядка 9 сек. что очень долго.

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

Возможно есть базы данных или структуры данных которые больше подходят для такого рода задач? Или надо смотреть уже в сторону создания собственных методов индексирования?
 
Последнее редактирование:

Gas

может по одной?
а какие индексы на таблице вообще есть?
покажи show create table;
проводил ли ты анализ самых частых запросов, могу предположить что составные индексы (city_from,city_to) и (city_from,country_to) будут одиними из самых востребованых.
innodb_pool_buffer_size стоит хоть не дефолтное значение 8MB ?

я бы с этой таблицей поигрался, только с более-менее полной, от 500K записей, а на 10 записях ничего не понятно будет.
 

riff

Новичок
А если
CREATE TEMPORARY TABLE ... AS
SELECT * FROM trips
WHERE `city_from`=39 and `country_to`=2

А дальше хоть MIN получай, хоть групируй, хоть удаляй, ... ?
 
Последнее редактирование:

StVolodymyr

Новичок
я немного соврал по скорости выполнения... запрос выполняеться 4-5 сек, а хотелось бы меньше 1 сек.

индекс не поможет в запросе без параметров когда идет перебор всей таблицы
Код:
SELECT `trips_key`, `trips_key` FROM
            (
              SELECT
                `trips_key`,
                (@rn := IF(@prev = `mask`, @rn + 1, 1)) AS `row_num`,
                (@prev := `mask`) AS `mask`,
                `price`
              FROM
                `travel_site`.`trips` AS t1,
                (SELECT @prev := NULL, @rn := 0) AS vars
              ORDER BY `mask`, `price`
            ) AS t2
        WHERE row_num < 2;
а `mask`, `price` у меня проиндексировано

innodb_pool_buffer_size у меня 256М с большим не стартует... на сервере 2ГБ памяти, но там есть еще пожиратели памяти.
неужели вариант только мощнее сервер?
 

Gas

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

>неужели вариант только мощнее сервер?
ещё кешировать или денормализовать (тоже по сути кеш) - вынести в отдельную таблицу промежуточные вычисления, те-же группы mask

а сколько записей этот запрос возвращает и как часто данные меняются?
 

StVolodymyr

Новичок
хм, запрос по миллиону записей без условий - оригинально.

>неужели вариант только мощнее сервер?
ещё кешировать или денормализовать (тоже по сути кеш) - вынести в отдельную таблицу промежуточные вычисления, те-же группы mask

а сколько записей этот запрос возвращает и как часто данные меняются?
Запрос без параметров, это когда пользователь не выбирает никаких пераметров поиска... показать самое дешовое неважно что.

сейчас запрос возвращает порадка 13-14 тис записей и они кешируються в мемкеше
 

Gas

может по одной?
Ну кеширование есть, можно експериментировать со sphinx'ом через sphinxQL, группировка там есть и вроде как шустро работает.

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

StVolodymyr

Новичок
обязательно посмотрю на Sphinx, но так как у меня все фактически цыфровые и это запрос мне должен вернуть только айдишки на которые я потом уже другим запросом буду навешивать данные, то решение оказалось очень необычным))

http://sqlfiddle.com/#!2/e2e0a/57

Код:
SELECT min(round(sort_field+id/100,2)) as `value`
FROM `test`
GROUP BY `group_field`;
потом уже на стороне php я легко вытаскиваю с дробной части нужные мне айдишки... на моих даных работает меньше секунды
 
Сверху