Какой из запросов более оптимальный?

Question

[Петр Питерский]

Один запрос с подзапросами

SELECT * FROM users WHERE id IN ( SELECT user FROM pp1 where organization = 1) OR id IN ( SELECT user FROM pp2 where organization = 1) LIMIT 15;

Второй с джоинами

SELECT u.* FROM users u LEFT JOIN pp1 ON u.id = pp1.id and pp1.organization = 1 LEFT JOIN pp2 ON u.id = pp2.id and pp2.organization = 1 WHERE pp1.user IS NOT NULL OR pp2.user IS NOT NULL LIMIT 15;

Но вот когда добавляю сортировку допустим к запросу

SELECT * FROM users WHERE id IN ( SELECT user FROM pp1 where organization = 1) OR id IN ( SELECT user FROM pp2 where organization = 1) ORDER BY u.name LIMIT 15;

запрос раз в 5 медленней идёт

Comments

[Everything_is_bad]

Домашнее задание: найти информацию что такое "план запроса", применить полученную информацию к своей проблеме

[Петр Питерский]

Everything_is_bad,

делал explain
1 PRIMARY users NULL ALL NULL NULL NULL NULL 732810 100.00 Using where
3 SUBQUERY pp1 NULL ref organization,user,User And Organization,User&Office organization 5 const 52 100.00 NULL
2 SUBQUERY pp2 NULL ref User And Organization,user,organization User And Organization 4 const 10 100.00 Using index

Беспокоит что из users идёт ALL

[Дмитрий]

запрос раз в 5 медленней идёт

ну естественно потому что без order by ему можно взять первые 15 записей, а order by взять все записи, отсортировать их и после этого взять 15. Как вы из массива возьмете 15 самых больших значений не просмотрев все значения массива?

Беспокоит что из users идёт ALL

больше интересно почему он индекс не использует.

[alexalexes]

Добавьте индекс для поля u.name, и сортировка будет незаметна.
PS: У pp.id, pp.organization индексы есть, надеюсь. u.id - создан как первичный ключ и обладает своим индексом, а не просто название поля?

[Петр Питерский]

alexalexes, в том то и дело что u.name есть индекс! но сама таблица большая(

[Петр Питерский]

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

[Петр Питерский]

alexalexes, все индексы естественно присутствуют

[Дмитрий]

Петр Питерский, ну во-первых вам стоит посмотреть alexalexes особенно ту часть которая PS касательно однотипных таблиц. Как бы заниматься оптимизацией запросов в заведомо ошибочной структуре дело убыточное. Чисто теоретически не факт что если вы не приведете свою выборку к нормальному состоянию по первичному ключу - что вам потребуется оптимизация сортировки. Но в принципе

[Дмитрий]

Петр Питерский, У вас похоже вообще никакие индексы не используются потому что оптимизатор считает что ему так или иначе выбирать все записи из таблицы пользователей и зачем ему тогда индексы юзать? дополнительная возня только

[Петр Питерский]

Дмитрий, таблицы не однотипные они содержат разные свойства разных типов пользователей

[Дмитрий]

Петр Питерский, ну по вашим запросам они выглядят однотипно, я конечно не видя их структуру могу ошибаться. Но окей, попробуйте index hint. Но блин хоть вы треснете - выглядит ровно так что надо исправить структуру

[alexalexes]

Ну, тогда поэтапно разбираться, где планировщик заруливает не туда.
Например, посмотреть план запроса:

SELECT u.*
  FROM users u 
ORDER BY name
LIMIT 15

Потом:

SELECT u.* FROM users u 
JOIN pp1 ON u.id = pp1.id
ORDER BY name
LIMIT 15

Потом:

SELECT u.* FROM users u 
JOIN pp1 ON u.id = pp1.id and pp1.organization = 1       
ORDER BY name
LIMIT 15

Посмотреть тоже самое с pp2.

[Петр Питерский]

alexalexes,
по первому
----------------------
1 SIMPLE u NULL index NULL name 202 NULL 15 100.00 NULL

по второму
----------------------
1 SIMPLE u NULL index PRIMARY name 202 NULL 15 100.00 NULL
1 SIMPLE pp1 NULL ref user,User And Organization,User&Office User And Organization 5 u.id 1 100.00 Using index

по третьему
----------------------

1 SIMPLE pp1 NULL ref organization,user,User And Organization,User&Office organization 5 const 52 100.00 Using where; Using temporary; Using filesort
1 SIMPLE u NULL eq_ref PRIMARY PRIMARY 4 pp1.user 1 100.00 NULL

по таблице 2

1 SIMPLE pp2 NULL index user user 4 NULL 1466 100.00 Using index; Using temporary; Using filesort
1 SIMPLE u NULL eq_ref PRIMARY PRIMARY 4 pp2.user 1 100.00 NULL

1 SIMPLE pp2 NULL ref User And Organization,user,organization User And Organization 4 const 10 100.00 Using index; Using temporary; Using filesort
1 SIMPLE u NULL eq_ref PRIMARY PRIMARY 4 pp2.user 1 100.00 NULL

Все по тестам неплохо, и вместе тоже с подзапросом или иннер джоин но когда идёт выборка с сортировкой((

[alexalexes]

А с кодировкой сравнения в таблицах все в порядке - совпадает с фактически используемой при записи данных?

[Петр Питерский]

alexalexes,

Везде стоит utf8mb4_unicode_ci

Answer 1

[alexalexes]

Использование in нужно избегать, если вы в нем не прописываете статичные значения, или известное кол-во переменных с альтернативными вариантами.
Более живенько будет работать с обычными join:

SELECT u.* FROM users u 
JOIN pp1 ON u.id = pp1.id and pp1.organization = 1 
union
SELECT u.* FROM users u 
JOIN pp2 ON u.id = pp2.id and pp2.organization = 1
ORDER BY name
LIMIT 15

Или использовать exists в where - так вы планировщику даете понять, что можно не искать все варианты в подзапросе, а найти один, подходящий под условие.

SELECT u.* FROM users u 
where exists (select 1 from pp1 where u.id = pp1.id and pp1.organization = 1 )
     or exists (select 1 from pp2 where u.id = pp2.id and pp2.organization = 1 )
ORDER BY u.name
LIMIT 15

PS: Наличие однотипных таблиц pp1 и pp2 говорит о наличии ошибки проектирования базы данных. От видов сущностей pp у вас не должно плодится таблицы с pp. У таблицы pp должно быть свойство вида сущности, например pp_type_id и таблица-справочник pp_type - куда вы будете добавлять новый вид сущности, а не создавать новую таблицу.
Тогда запрос у вас будет выглядеть так:

SELECT u.*
   FROM users u 
where exists (select 1 
              from pp
             where u.id = pp.id
               and pp.organization = 1 
               and pp.pp_type_id in (1, 2) -- собственно, в таблице содержится все, что раньше находилось в pp1 и pp2, появилось поле, которое отличает разновидности записей
          )
ORDER BY u.name
LIMIT 15

Comments

[Петр Питерский]

pp1 - пользвоатели
pp2 - сотрудники
в этих таблицах разные данные

[Петр Питерский]

в том и дело что мне надо найти не один результат а много

[Михаил Ливач]

Петр Питерский, если Вы среагировали на (select 1 from
то речь была о другом. Строк в результате будет столько, сколько нужно по условию

Answer 2

[unfilled]

я бы сделал так

select *
from users u
where 
    u.id = any (
        select id
        from pp1
        where organization = 1

        union all

        select id
        from pp2
        where organization = 1
    )
order by u.name
limit 15

если работает медленно - уже смотреть план выполнения