Форум пользователей MySQL

Спасибо.

Explain пишет все Ок.

Хорошая идея.
Проверил - один раз.

Не совсем точно сформулировал исходный запрос. Правильно выглядит так

SELECT distinct t1.id_t2
FROM t1
WHERE t1.id_t2 NOT IN
         (SELECT t2.id_t2 FROM t2, t1
          WHERE t1.id_t1 = $param1 AND
                t2.id_pr = t1.id_t2 AND
                t2.id_pr <> $param2) ;

Предлагаю использовать новые bbcode-теги {syntax=mysql} код на mysql {/syntax} (фигурные скобки заменить на квадратные)

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

Но с точки зрения эффективности порочен.
Разложим его по полочкам

Выполним только сам подзапрос

mysql> SELECT t2.id_t2 FROM t2, t1
    ->           WHERE t1.id_t1 = 1 AND
    ->                 t2.id_pr = t1.id_t2 AND
    ->                 t2.id_pr <> 1;
+-------+
| id_t2 |
+-------+
|     4 |
+-------+
1 row in set (0.03 sec)

mysql>

Он возвращает одну строку и выполняется за 0.03 сек

Слегка видоизменим его. Добавим задержку в 1 сек к каждой возвращаемой строке.

mysql> SELECT t2.id_t2+sleep(1) FROM t2, t1
    ->            WHERE t1.id_t1 = 1 AND
    ->                  t2.id_pr = t1.id_t2 AND
    ->                  t2.id_pr <> 1;
+-------------------+
| t2.id_t2+sleep(1) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (1.02 sec)

mysql>

Получили как и следовало ожидать чуть больше 1 секунды время выполнения.

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

mysql> SELECT distinct t1.id_t2
    -> FROM t1
    -> WHERE t1.id_t2 NOT IN
    ->          (SELECT t2.id_t2 + sleep(1)  FROM t2, t1
    ->           WHERE t1.id_t1 =1 AND
    ->                 t2.id_pr = t1.id_t2 AND
    ->                 t2.id_pr <> 1) ;
+-------+
| id_t2 |
+-------+
|     1 |
|     2 |
|     3 |
+-------+
3 rows in set (27.03 sec)

mysql>

Караул. Время выполнения 27 сек.
Посмотрим откуда они растут.
Выполним основной запрос прибив дистинкт и ограничения накладываемые подзапросом

mysql> SELECT t1.id_t2
    -> FROM t1
    -> ;
+-------+
| id_t2 |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     1 |
|     2 |
|     3 |
+-------+
7 rows in set (0.02 sec)

mysql>

Итого семь записей.
Как в ссылке в начале топика звучало, можно было бы предположить - подзапрос вызывается каждый раз для сравнения строки с результатом подзапроса. Но тогда он должен вызываться семь раз и время выполнения должно быть чуть больше семи секунд. Это само по себе неприемлемо. Что будет при работе с реальными данными, при 100 - 300К записей.
Картина же намного хуже. Непонятно почему запрос выполняется 27 сек.

Ну и резюме, данный запрос нужно выполнить без использования подзапроса. А тут я уже голову сломал.

Подтормаживаю.
Спасибо.

Попробуйте такой запрос. Если не получится, выложите дамп тестовой таблицы.

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

PHP не хотелось бы прикручивать сюда. А вот если можно запросу передать результаты выполнения ХП, то возможно оно то что нужно. Пошел мануал читать.

Хранимую процедуру можно написать для такой цели. А вы уверены, что этот запрос у вас медленный? Преждевременная оптимизация - корень всех зол.

Правильно ли я понял.
Если ХП возвращает набор записей, то использовать я его могу только на клиенте. Но не на сервере.

Курсоры смущают своей неспешностью.
GROUP_CONCAT тоже страшновато.
Максимальный размер строки в моем случае 21К - UTF8, боюсь возможны ситуации будет больше.

Кажется въезжать начинаю почему некорректно работает запрос с join'ом
Попытаюсь деградировать на самый нижний уровень и тупо разложить все по полочкам

mysql> select * from t1;
+-------+-------+
| id_t1 | id_t2 |
+-------+-------+
|     1 |     1 |
|     1 |     2 |
|     1 |     3 |
|     1 |     4 |
|     1 |     7 |
|     2 |     1 |
|     2 |     3 |
|     2 |     5 |
|     2 |     6 |
|     2 |     7 |
|     2 |     8 |
+-------+-------+
11 rows in set (0.00 sec)

mysql> select * from t2;
+-------+-------+
| id_t2 | id_pr |
+-------+-------+
|     1 |     0 |
|     2 |     1 |
|     3 |     1 |
|     4 |     2 |
|     5 |     3 |
|     6 |     4 |
|     7 |     5 |
|     8 |     6 |
|     6 |     2 |
|     6 |     1 |
|     6 |     0 |
+-------+-------+
11 rows in set (0.00 sec)

mysql> SELECT distinct t1.id_t2
    -> FROM t1
    -> WHERE t1.id_t2 NOT IN
    ->          (SELECT t2.id_t2 FROM t2, t1
    ->           WHERE t1.id_t1 = 1 AND
    ->                 t2.id_pr = t1.id_t2 AND
    ->                 t2.id_pr <> 1) ;
+-------+
| id_t2 |
+-------+
|     1 |
|     2 |
|     3 |
|     7 |
|     8 |
+-------+
5 rows in set (0.06 sec)

 

Теперь пойдем по порядку
Выполним только подзапрос c единственным ограничением. Только записи в которых t2.id_pr = t1.id_t2.

mysql> SELECT * FROM t2, t1
    -> WHERE t2.id_pr = t1.id_t2;
+-------+-------+-------+-------+
| id_t2 | id_pr | id_t1 | id_t2 |
+-------+-------+-------+-------+
|     2 |     1 |     1 |     1 |
|     3 |     1 |     1 |     1 |
|     6 |     1 |     1 |     1 |
|     4 |     2 |     1 |     2 |
|     6 |     2 |     1 |     2 |
|     5 |     3 |     1 |     3 |
|     6 |     4 |     1 |     4 |
|     2 |     1 |     2 |     1 |
|     3 |     1 |     2 |     1 |
|     6 |     1 |     2 |     1 |
|     5 |     3 |     2 |     3 |
|     7 |     5 |     2 |     5 |
|     8 |     6 |     2 |     6 |
+-------+-------+-------+-------+
13 rows in set (0.03 sec)
 

Добавим еще одно условие.  t2.id_pr <> 1

mysql> SELECT * FROM t2, t1
    ->               WHERE t2.id_pr = t1.id_t2 AND
    ->                               t2.id_pr <> 1;
+-------+-------+-------+-------+
| id_t2 | id_pr | id_t1 | id_t2 |
+-------+-------+-------+-------+
|     4 |     2 |     1 |     2 |
|     6 |     2 |     1 |     2 |
|     5 |     3 |     1 |     3 |
|     6 |     4 |     1 |     4 |
|     5 |     3 |     2 |     3 |
|     7 |     5 |     2 |     5 |
|     8 |     6 |     2 |     6 |
+-------+-------+-------+-------+
7 rows in set (0.02 sec)

 

И наконец  t1.id_t1 = 1

mysql> SELECT * FROM t2, t1
    ->               WHERE t2.id_pr = t1.id_t2 AND
    ->                               t2.id_pr <> 1 AND
    ->                               t1.id_t1 = 1 ;
+-------+-------+-------+-------+
| id_t2 | id_pr | id_t1 | id_t2 |
+-------+-------+-------+-------+
|     4 |     2 |     1 |     2 |
|     6 |     2 |     1 |     2 |
|     5 |     3 |     1 |     3 |
|     6 |     4 |     1 |     4 |
+-------+-------+-------+-------+
4 rows in set (0.02 sec)
 

Если выполнить только сам подзапрос, получиться

mysql> SELECT t2.id_t2 FROM t2, t1
    -> WHERE t1.id_t1 = 1 AND
    ->                  t2.id_pr = t1.id_t2 AND
    ->                  t2.id_pr <> 1;
+-------+
| id_t2 |
+-------+
|     4 |
|     6 |
|     5 |
|     6 |
+-------+
4 rows in set (0.05 sec)
 

t1.id_t2 NOT IN к этому набору
+-------+-------+
| id_t1 | id_t2 |
+-------+-------+
|     1 |     1 |
|     1 |     2 |
|     1 |     3 |
|     1 |     7 |
|     2 |     1 |
|     2 |     3 |
|     2 |     7 |
|     2 |     8 |

DISTINCT на результат
+-------+-------+
| id_t1 | id_t2 |
+-------+-------+
|     1 |     1 |
|     1 |     2 |
|     1 |     3 |
|     1 |     7 |
|     2 |     8 |


Теперь поковыряемся в

rgbeast написал:

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

SELECT DISTINCT t11.id_t2      
FROM t1 AS t11
LEFT JOIN t2 ON t11.id_t2=t2.id_t2 AND t2.id_pr<>1
LEFT JOIN t1 ON t2.id_pr=t1.id_t2 AND t1.id_t1=1
WHERE t1.id_t2 IS NULL;
 

Посмотрим что отдает первый джойн

mysql> SELECT *
    ->      FROM t1 AS t11
    ->      LEFT JOIN t2 ON t11.id_t2=t2.id_t2 AND t2.id_pr<>1;
+-------+-------+-------+-------+
| id_t1 | id_t2 | id_t2 | id_pr |
+-------+-------+-------+-------+
|     1 |     1 |     1 |     0 |
|     1 |     2 |  NULL |  NULL |
|     1 |     3 |  NULL |  NULL |
|     1 |     4 |     4 |     2 |
|     1 |     7 |     7 |     5 |
|     2 |     1 |     1 |     0 |
|     2 |     3 |  NULL |  NULL |
|     2 |     5 |     5 |     3 |
|     2 |     6 |     6 |     4 |
|     2 |     6 |     6 |     2 |
|     2 |     6 |     6 |     0 |
|     2 |     7 |     7 |     5 |
|     2 |     8 |     8 |     6 |
+-------+-------+-------+-------+
13 rows in set (0.05 sec)
 

Слегка переиначим запрос. Выведем все поля и уберем условие WHERE t1.id_t2 IS NULL

mysql> SELECT *
    -> FROM t1 AS t11
    -> LEFT JOIN t2 ON t11.id_t2=t2.id_t2 AND t2.id_pr<>1
    -> LEFT JOIN t1 ON t2.id_pr=t1.id_t2 AND t1.id_t1=1;
+-------+-------+-------+-------+-------+-------+
| id_t1 | id_t2 | id_t2 | id_pr | id_t1 | id_t2 |
+-------+-------+-------+-------+-------+-------+
|     1 |     1 |     1 |     0 |  NULL |  NULL |
|     1 |     2 |  NULL |  NULL |  NULL |  NULL |
|     1 |     3 |  NULL |  NULL |  NULL |  NULL |
|     1 |     4 |     4 |     2 |     1 |     2 |
|     1 |     7 |     7 |     5 |  NULL |  NULL |
|     2 |     1 |     1 |     0 |  NULL |  NULL |
|     2 |     3 |  NULL |  NULL |  NULL |  NULL |
|     2 |     5 |     5 |     3 |     1 |     3 |
|     2 |     6 |     6 |     4 |     1 |     4 |
|     2 |     6 |     6 |     2 |     1 |     2 |
|     2 |     6 |     6 |     0 |  NULL |  NULL |
|     2 |     7 |     7 |     5 |  NULL |  NULL |
|     2 |     8 |     8 |     6 |  NULL |  NULL |
+-------+-------+-------+-------+-------+-------+
13 rows in set (0.02 sec)
 

Запись которая нам не нужна
+-------+-------+-------+-------+-------+-------+
| id_t1 | id_t2 | id_t2 | id_pr | id_t1 | id_t2 |
+-------+-------+-------+-------+-------+-------+
|     2 |     6 |     6 |     0 |  NULL |  NULL |

В подзапросе она была прибита из выдачи условием  t2.id_pr = t1.id_t2 и больше нигде не мешалась.
В варианте с джойном она не проходит условие t2.id_pr=t1.id_t2 и естественно id_t2 получает NULL  и соответственно выдается по WHERE t1.id_t2 IS NULL.

Про как с этим бороться мыслей пока нет.