Как правильно сформировать запрос к mysql?

Question

[Данил]

Есть масив ID Вида [123321, 154874, 12233] и тд.
Есть база в которой записаны эти ID. Как мне сделать запрос к базе, чтобы мне вернулись только те ID, которых нету в базе?

Answer 1

[Максим Федоров]

SELECT * FROM tablename WHERE id NOT IN (123321, 154874, 12233)

Answer 2

[Евгений Безымянников]

Select * From xxx Where xxx.id NOT IN (123321, 154874, 12233);
При большом числе записей будут тормоза еще те.

Comments

[landergate]

Тормозов не должно быть, у колонки скорее всего PRIMARY KEY

[Евгений Безымянников]

landergate: и что в того? Он фулсканом будет ходить по нему и добавлять в ответ все что не совпадает со списком id. Число проверок будет равно числу строк* числу элементов в запросе.

[landergate]

Евгений Безымянников: i.imgur.com/xBxJqv2.png

Время исполнения селекта в таблице на 15кк значений:
/* Affected rows: 0 Found rows: 3 Warnings: 0 Duration for 1 query: 0,047 sec. */

Для этого и нужны индексы. =)

[Евгений Безымянников]

landergate: Not in - вот что нужно проверить, а не IN

[landergate]

Евгений Безымянников: Вы правы, но достаточно указать LIMIT, чтобы запрос стал идентичен по скорости.

Так-то любая выборка, независимо от IN/не IN, будет выдаваться ровно столько, сколько значений в таблице, если она ничем не ограничена.

[landergate]

Евгений Безымянников: Выборка 500 значений по трём NOT IN из другой таблицы в 15кк записей:
Duration for 1 query: 0,016 sec.

[Евгений Безымянников]

landergate: пример запроса - 100 тыс статей и в списке "не видеть" 120 статей, как на лепре/дерти .

[Евгений Безымянников]

landergate: условия на лимиты не было

[landergate]

Евгений Безымянников: Выдача большого количества результатов займёт какое-то время в любом сценарии, независимо от того, как происходит выборка. NOT IN в этом случае на скорость не влияет. =)

[Евгений Безымянников]

landergate: в случае с in - получим записей максимум по числу элементов в запросе, в not in все кроме запроса. Разница в порядки. In - по ключу, not in - фулскан

[landergate]

Евгений Безымянников: На самом деле Вы правы про то, что при NOT IN MySQL решает выгрузить всю таблицу, поскольку исключённых NOT IN всё-равно будет меньше, чем остальной результат.

На практике я, впрочем, не столкнулся со значительным снижением скорости запроса, даже в том примере про 100 тысяч статей, из которых нужно скрыть 120.

Параметры взял даже больше, 150к статей и не видим 200 статей:

SELECT * FROM `160226_logs`.`picklog` WHERE `id` NOT IN (SELECT `hide_id` FROM `160331_logs`.`hide`) LIMIT 150510;
/* Affected rows: 0  Found rows: 150 510  Warnings: 0  Duration for 1 query: 0,031 sec. (+ 0,499 sec. network) */

(это не SSD и не кэш)

Так что тезис про тормоза могут быть преувеличены, надо рассматривать ситуацию на месте.

[Евгений Безымянников]

landergate: а запрос без not in?

[landergate]

Евгений Безымянников:

/* Affected rows: 0  Found rows: 150 510  Warnings: 0  Duration for 1 query: 0,047 sec. (+ 0,343 sec. network) */
/* Affected rows: 0  Found rows: 250 510  Warnings: 0  Duration for 1 query: 0,016 sec. (+ 0,593 sec. network) */
/* Affected rows: 0  Found rows: 1 250 510  Warnings: 0  Duration for 1 query: 0,015 sec. (+ 2,855 sec. network) */

[Евгений Безымянников]

landergate: забавно что что чем больше записей то быстрее запрос)

[landergate]

Евгений Безымянников: Типа в память часть загрузилась.
Сейчас попробую на принципиально разных таблицах, каждый раз.
Количество строк там варьируется от 8 до 15 миллионов.

[Евгений Безымянников]

landergate: no cashe поможет

[landergate]

Евгений Безымянников:

/* Affected rows: 0  Found rows: 750 510  Warnings: 0  Duration for 1 query: 0,109 sec. (+ 1,653 sec. network) */
/* Affected rows: 0  Found rows: 1 750 510  Warnings: 0  Duration for 1 query: 0,156 sec. (+ 4,321 sec. network) */
/* Affected rows: 0  Found rows: 3 351 829  Warnings: 0  Duration for 1 query: 0,109 sec. (+ 7,722 sec. network) */

Чтож такое... =)

[landergate]

Евгений Безымянников: Хорошая мысль про NO CACHE. Сейчас посмотрим~

[Евгений Безымянников]

По лимиту быстро режет.

[landergate]

Евгений Безымянников: Таблица на 157кк строк с SQL_NO_CACHE:

/* Affected rows: 0  Found rows: 1 750 510  Warnings: 0  Duration for 1 query: 0,062 sec. (+ 3,167 sec. network) */
/* Affected rows: 0  Found rows: 3 750 510  Warnings: 0  Duration for 1 query: 0,015 sec. (+ 6,662 sec. network) */
/* Affected rows: 0  Found rows: 4 750 510  Warnings: 0  Duration for 1 query: 0,015 sec. (+ 8,612 sec. network) */

[landergate]

Евгений Безымянников: Ощущение, что тесты мои не чисты - ФС кэширует чтение после первого открытия таблицы.

Не суть важно. В любом случае, всё это - достаточный материал для размышления Данил

Answer 3

[Melkij]

Mysql? Неудобно.

select /**/ from (
    select 123321 as id
    union all
    select 154874
    union all
    select 12233
) as targets
where not exists (
    select 1 from tablename where tablename.id = targets.id
);