ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Ограничение джойнов по числу записей
9 сообщений из 9, страница 1 из 1
Ограничение джойнов по числу записей
    #39754827
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
paver
Гость
Чисто спортивный интерес.

Имеется работающий запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT CONCAT(u.last_name, ' ', u.first_name) as agent_fullname, COUNT(DISTINCT h.ticket_id) kol
FROM ticket_history h
JOIN users u ON u.id = h.create_by
JOIN user_preferences up ON up.user_id = h.create_by
WHERE h.create_time > CURRENT_DATE - INTERVAL 10 DAY
      AND up.preferences_key = 'UserComment' 
      AND up.preferences_value = 'tag1'
GROUP BY h.create_by
ORDER BY kol DESC


Но работает только при небольших значениях периода. Если интервал больше 15 дней, сервер матерится:
Код: plaintext
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

Попытка обмануть систему путем перемещения ограничений из фильтра в условия связывания ожидаемо результата не дала.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT CONCAT(u.last_name, ' ', u.first_name) as agent_fullname, COUNT(DISTINCT h.ticket_id) kol
FROM users u
JOIN user_preferences up ON up.user_id = u.id
     AND up.preferences_key = 'UserComment' 
     AND up.preferences_value = 'tag1'
JOIN ticket_history h ON u.id = h.create_by
     AND h.create_time > CURRENT_DATE - INTERVAL 10 DAY
GROUP BY h.create_by
ORDER BY kol DESC


Ошибка та же.

А вот если основную таблицу обернуть вложенным селектом, то итоговый запрос вполне справляется с гораздо большим интервалом.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT CONCAT(u.last_name, ' ', u.first_name) as agent_fullname, COUNT(h.ticket_id) kol
FROM (SELECT DISTINCT create_by, ticket_id
      FROM ticket_history
      WHERE create_time > CURRENT_DATE - INTERVAL 60 DAY
     ) h
JOIN users u ON u.id = h.create_by
JOIN user_preferences up ON up.user_id = h.create_by
WHERE up.preferences_key = 'UserComment' 
      AND up.preferences_value = 'tag1'
GROUP BY h.create_by
ORDER BY kol DESC



Я думал, может это count(DISTINCT ...) так затратно работает. Но нет, если дистинкты ото всюду убрать, результат будет (другой, но) аналогичный.

Вопрос: можно ли как-то (настройки, индексы, директивы) заставить первый запрос работать более эффективно?
...
Рейтинг: 0 / 0
29.12.2018, 10:53
| Ответить | Цитировать | Написать  
Ограничение джойнов по числу записей
    #39754837
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
paverобмануть систему путем перемещения ограничений из фильтра в условия связыванияНу это Вы только себя обманываете. Почитайте, как выполняются запросы - оба этих запроса фактически разворачиваются в одну и ту же картезианку со всеми условиями отбора во WHERE.
paverА вот если основную таблицу обернуть вложенным селектомНадеюсь, Вы понимаете, что такой запрос неэквивалентен исходному - по причине наличия DISTINCT в подзапросе?
paverсервер материтсяСервер не только матерится, но и вполне вменяемо советует, как можно уговорить его не материться.
...
Рейтинг: 0 / 0
29.12.2018, 11:19
| Ответить | Цитировать | Написать  
Ограничение джойнов по числу записей
    #39754853
Swa111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Swa111
Гость
paver,

Попробуйте так:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT CONCAT(u.last_name, ' ', u.first_name) as agent_fullname, kol
FROM 
 (select create_by, COUNT(ticket_id) kol
  from ticket_history 
  where 
   ticket_history.create_time > CURRENT_DATE - INTERVAL 10 DAY
   and exists(select 1 from user_preferences up where up.user_id = create_by and AND up.preferences_key = 'UserComment' 
      AND up.preferences_value = 'tag1')
  group by create_by
 ) h
JOIN users u ON u.id = h.create_by
ORDER BY kol DESC



в общем старайтесь агрегатные функции выполнить на меньшем объеме данных, т.е. выполнение COUNT(DISTINCT h.ticket_id) это плохо, когда можно выполнить COUNT(ticket_id) на более низком уровне

необходимо наличие индексов на ticket_history.create_time; users.id; user_preferences.user_id (в идеале индекс user_preferences(user_id, preferences_key, preferences_value).

если условие up.preferences_key = 'UserComment' AND up.preferences_value = 'tag1' отсеивает большую часть пользователей то лучше покажет себя следующий набор индексов
user_preferences(preferences_key, preferences_value); ticket_history(create_time, create_by)
и запрос вида

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT CONCAT(u.last_name, ' ', u.first_name) as agent_fullname, kol
FROM 
 (select create_by, COUNT(ticket_id) kol
  from 
    (select distinct user_id from user_preferences up where up.preferences_key = 'UserComment' 
      AND up.preferences_value = 'tag1') up
    inner join ticket_history on up.user_id = ticket_history.create_by 
  where 
   ticket_history.create_time > CURRENT_DATE - INTERVAL 10 DAY
  group by create_by
 ) h
JOIN users u ON u.id = h.create_by
ORDER BY kol DESC
...
Рейтинг: 0 / 0
29.12.2018, 11:55
| Ответить | Цитировать | Написать  
Ограничение джойнов по числу записей
    #39754885
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
paver
Гость
Akinapaverобмануть систему путем перемещения ограничений из фильтра в условия связыванияНу это Вы только себя обманываете.
Не обманываюсь ) Я же написал - ожидаемо

AkinapaverА вот если основную таблицу обернуть вложенным селектомНадеюсь, Вы понимаете, что такой запрос неэквивалентен исходному - по причине наличия DISTINCT в подзапросе?
Полагаю, что эквивалентны - по причине наличия DISTINCT в COUNT в исходном запросе. По крайней мере, результаты запросов эквивалентны.
Кроме того, я отметил, что если избавиться от дистинктов, результат будет аналогичным, т.е. первый выполняется с ошибкой, третий - без.

Akinapaverсервер материтсяСервер не только матерится, но и вполне вменяемо советует, как можно уговорить его не материться.
Да, но! Совет заключается (как я понимаю) в увеличении выделенных ресурсов. Третий запрос обходится без этого. Означает ли это то, что запрос с подзапросом оптимальнее с точки зрения потребления ресурсов ОП?
...
Рейтинг: 0 / 0
29.12.2018, 12:57
| Ответить | Цитировать | Написать  
Ограничение джойнов по числу записей
    #39754900
Swa111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Swa111
Гость
paver,

автор Означает ли это то, что запрос с подзапросом оптимальнее с точки зрения потребления ресурсов ОП?

Да, если это значительно сократит выборку (хотя в случае с нормально составленными индексами оптимизатор сам справится)

Но все зависит от конкретной задачи, объемов данных и т.п.
...
Рейтинг: 0 / 0
29.12.2018, 13:12
| Ответить | Цитировать | Написать  
Ограничение джойнов по числу записей
    #39754907
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
paver
Гость
Swa111в общем старайтесь агрегатные функции выполнить на меньшем объеме данных, т.е. выполнение COUNT(DISTINCT h.ticket_id) это плохо, когда можно выполнить COUNT(ticket_id) на более низком уровне

Ну да, примерно это и сделано в третьем запросе. Основной массив данных фильтруется в подзапросе, а вторая и третья таблицы - это скорее справочники.
...
Рейтинг: 0 / 0
29.12.2018, 13:22
| Ответить | Цитировать | Написать  
Ограничение джойнов по числу записей
    #39754914
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
paver
Гость
Swa111paver,

автор Означает ли это то, что запрос с подзапросом оптимальнее с точки зрения потребления ресурсов ОП?

Да, если это значительно сократит выборку (хотя в случае с нормально составленными индексами оптимизатор сам справится)

Но все зависит от конкретной задачи, объемов данных и т.п.

С конкретной задачей - понятно. Я имел в виду общий случай. Почему корректные "чистые" джойны работают хуже подзапросов?
Т.е. как заставить первый запрос работать не хуже третьего?
...
Рейтинг: 0 / 0
29.12.2018, 13:28
| Ответить | Цитировать | Написать  
Ограничение джойнов по числу записей
    #39754918
Фотография Karbafos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Karbafos
Участник
paverТ.е. как заставить первый запрос работать не хуже третьего?
что explain показывает для первого запроса?
...
Рейтинг: 0 / 0
29.12.2018, 13:33
| Ответить | Цитировать | Написать  
Ограничение джойнов по числу записей
    #39754964
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
paver
Гость
Karbafosчто explain показывает для первого запроса?
Увы, если что и показывает, то уже после нового года ))
...
Рейтинг: 0 / 0
29.12.2018, 14:57
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Ограничение джойнов по числу записей
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=41&tid=1829386]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 86ms
get topic data:
 13ms
get forum data:
 2ms
get page messages:
 70ms
get tp. blocked users:
 2ms
others: 314ms
total:  517ms
созд. / загр.: 517ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]