Простой SQL запрос

[sv2109]

Столкнулся с интересной задачей в одном проекте над которым сейчас работаю. 

 

Есть таблица (упрощенный ее вариант):

CREATE TABLE `test` (

  `id` int(11) NOT NULL,

  `word` varchar(64) CHARACTER SET utf8 NOT NULL,

  UNIQUE KEY `index` (`id`,`word`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8_general_ci;


INSERT INTO `test` (`id`, `word`) VALUES

(1, 'baz'),

(1, 'foo'),

(2, 'bar'),

(2, 'foo'),

(3, 'bar'),

(3, 'baz'),

(3, 'foo');

Задача: 

1. найти все 'id', у которых 'word' равно 'bar' И 'foo' (должно найти 2 и 3)

2. очень важно, чтобы запрос работал максимально быстро, так как в реальной таблице сотни тысяч записей

 

Казалось бы что может быть проще? 

Если нужна логика "или" то все действительно очень просто:

SELECT `id` FROM `test` WHERE `word` = 'bar' OR `word` = 'foo' GROUP BY `id`

Если же логика "и" то сразу хочется написать:

SELECT `id` FROM `test` WHERE `word` = 'bar' AND `word` = 'foo' GROUP BY `id`

но, такой запрос ничего не найдет, так как 'word' не может одновременно быть и  'bar' и 'foo'

 

И вот сижу и думаю.

Решения я нашел, но оно мне не нравится, так как оно сложное и работает раза в 3 медленнее, чем запрос с "OR"

Может есть какое-то простое и быстрое решение которого я в час ночи в упор не вижу?

 

[snastik]

Столкнулся с интересной задачей в одном проекте над которым сейчас работаю. 

 

Есть таблица (упрощенный ее вариант):

 

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `word` varchar(64) CHARACTER SET utf8 NOT NULL,
  UNIQUE KEY `index` (`id`,`word`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8_general_ci;

INSERT INTO `test` (`id`, `word`) VALUES
(1, 'baz'),
(1, 'foo'),
(2, 'bar'),
(2, 'foo'),
(3, 'bar'),
(3, 'baz'),
(3, 'foo');

Задача: 

1. найти все 'id', у которых 'word' равно 'bar' И 'foo' (должно найти 2 и 3)

2. очень важно, чтобы запрос работал максимально быстро, так как в реальной таблице сотни тысяч записей

 

Казалось бы что может быть проще? 

Если нужна логика "или" то все действительно очень просто:

SELECT `id` FROM `test` WHERE `word` = 'bar' OR `word` = 'foo' GROUP BY `id`

Если же логика "и" то сразу хочется написать:

SELECT `id` FROM `test` WHERE `word` = 'bar' AND `word` = 'foo' GROUP BY `id`

но, такой запрос ничего не найдет, так как 'word' не может одновременно быть и  'bar' и 'foo'

 

И вот сижу и думаю.

Решения я нашел, но оно мне не нравится, так как оно сложное и работает раза в 3 медленнее, чем запрос с "OR"

Может есть какое-то простое и быстрое решение которого я в час ночи в упор не вижу?

 

 

 

http://www.dofactory.com/sql/where-in

SELECT Id, CompanyName, City, Country
  FROM Supplier
 WHERE Country IN ('USA', 'UK', 'Japan')

Ну и если что, могу напомнить как поменять цвет кнопочки

.button(
   backround: red;
)

[markimax]

Еще так можно

SELECT `id` FROM `test` WHERE `word` in ('bar', 'foo') GROUP BY `id`

[sv2109]

мимо :)
это я с самого начала попробовал

`word` IN ('bar', 'foo') 
это аналог 
`word` = 'bar' OR  `word` = 'foo' 

А логика нужна "AND" 

запрос 
 

SELECT `id` FROM `test` WHERE `word` IN ('bar', 'foo') GROUP BY `id`

возвращает 1,2,3 
а нужно 2,3

[markimax]

мимо :)

это я с самого начала попробовал

`word` IN ('bar', 'foo') 

это аналог 

`word` = 'bar' OR  `word` = 'foo' 

А логика нужна "AND" 

запрос 

 

SELECT `id` FROM `test` WHERE `word` IN ('bar', 'foo') GROUP BY `id`

возвращает 1,2,3 

а нужно 2,3

 

Тогда надо Лефт Джойнить - а это будет медленнее и запрос громоздкий

И не сбивайте нас с толку, ID считается уникальным (я кстати сразу и не заметил "дубли" ID)

Переобзовите

[snastik]

Тогда надо Лефт Джойнить - а это будет медленнее и запрос громоздкий

И не сбивайте нас с толку, ID считается уникальным (я кстати сразу и не заметил "дубли" ID)

Переобзовите

 

вот Марк с языка снял!

[markimax]

вот Марк с языка снял!

А ты предыдущий снял пока я его набирал :ugeek:

[sv2109]

Тогда надо Лефт Джойнить - а это будет медленнее и запрос громоздкий

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

И не сбивайте нас с толку ID считается уникальным (я кстати сразу и не заметил "дубли" ID)

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

[markimax]

...

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

Да просто мы привыкли что ID уникальный всегда. Я даже не посмотрел что ID одинаковый у некоторых элементов и выдал код.

А так бы сразу выдал LJ

[n3bo]

Не знаю насколько оптимизированный, но как вариант:

SELECT `id` FROM test t WHERE (select count(*) from test t2 WHERE t2.word IN ('bar', 'foo') AND t2.id=t.id) >=2 GROUP BY t.test

[ZeroHero]

Как-то так.

SELECT t1.id
FROM test AS t1
INNER JOIN test AS t2
ON t1.id = t2.id AND t2.word = 'bar'
WHERE t1.word = 'foo'

[snastik]

 

Не знаю насколько оптимизированный, но как вариант:

SELECT `id` FROM test t WHERE (select count(*) from test t2 WHERE t2.word IN ('bar', 'foo') AND t2.id=t.id) >=2 GROUP BY t.test

 

Это уложит на 100 000 записей сервер на полдня, так как для каждой записи вы создаете итерацию подзапроса с выборкой из всей таблицы.

 

 

SELECT `id` FROM `test` WHERE `word` = 'bar' OR `word` = 'foo' GROUP BY `id` having count(*)=2

или вот так если у mysql ума хватит

SELECT `id` FROM `test` WHERE `word` = 'bar' OR `word` = 'foo' GROUP BY `id` having count(distinct `word`)=2

 

having - не самый быстрый метод,  а тем более по неиндексированной сущности.

 

 

 

Как-то так.

SELECT t1.id
FROM test AS t1
INNER JOIN test AS t2
ON t1.id = t2.id AND t2.word = 'bar'
WHERE t1.word = 'foo'

 

В данном контексте - это идеальный вариант.

[sv2109]

Как-то так.

SELECT t1.id
FROM test AS t1
INNER JOIN test AS t2
ON t1.id = t2.id AND t2.word = 'bar'
WHERE t1.word = 'foo'

 

У меня с самого начала был почти такой же вариант:

SELECT t.id FROM `test` t LEFT JOIN `test` t2 ON (t.id = t2.id) WHERE t.word = 'bar' AND t2.word = 'foo' GROUP BY t.id

Но он мне не нравится, потому что:

1. Я этого не указал в задании, думал и так понятно, но в реальном запросе не обязательно будет 2 слова, там может быть и 3 и 5 и 10, а иногда возможно даже и больше. 

2. В реальной базе не одна, а 2 таблицы и для каждого нового слова нужно подключать не одну, а 2 новых таблицы

3. В реальной таблице могут быть десятки и даже сотни тысяч записей. 

 

И если взять например таблицу с 100 тыс. записей, осуществить выборку по 10 словам (подключив по 2 доп. таблицы для каждого слова) то это очень сильно скажется на скорости запроса, запрос будет выполняться больше 10 раз медленнее, чем аналогичный с логикой "OR", так как в данном случае MySQL будет искать по каждой подключенной таблице отдельно. 

 

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

 

В любом случае, всем спасибо за потраченное время, оно не потрачено напрасно, мы все узнали что-то новое, получили новые знания, которые нам пригодятся в будущем. 

 

Тема открыта - если у кого-то будут новые идеи - пишите. 

[ZeroHero]

SELECT `tq`.`id` FROM (SELECT COUNT(*) AS `count`, `id` FROM `test` WHERE `word` IN ('foo', 'bar') GROUP BY `id`) AS `tq` WHERE `tq`.`count` = 2

где 2 - суммарное количество искомых слов.

[sv2109]

SELECT `tq`.`id` FROM (SELECT COUNT(*) AS `count`, `id` FROM `test` WHERE `word` IN ('foo', 'bar') GROUP BY `id`) AS `tq` WHERE `tq`.`count` = 2

где 2 - суммарное количество искомых слов.

 

во! а вот это уже круто :)

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

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

[markimax]

во! а вот это уже круто :)

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

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

На большой базе COUNT тоже мега тормоз и плюс временные таблицы... (про подсчет количества товаров в категориях напомнить ;) )

Иногда будет быстрее быстрыми простыми подзапросами сделать

Проверьте все варианты на большой базе и с большим набором слов

[markimax]

UNION еще с простыми запросами проверьте на большой базе

Тоже вариант

[sv2109]

На большой базе COUNT тоже мега тормоз и плюс временные таблицы... (про подсчет количества товаров в категориях напомнить ;) )

Иногда будет быстрее быстрыми простыми подзапросами сделать

Проверьте все варианты на большой базе и с большим набором слов

 

в одном случае мы имеем один COUNT, в другом 20 джойнов с отдельным поиском по каждой подключенной таблице, первый вариант однозначно будет быстрее и даже в разы. Если на большой таблице COUNT будет работать медленно то поиск по 20 таких же больших таблицах будет в разы медленнее. 

 

[sv2109]

SELECT `tq`.`id` FROM (SELECT COUNT(*) AS `count`, `id` FROM `test` WHERE `word` IN ('foo', 'bar') GROUP BY `id`) AS `tq` WHERE `tq`.`count` = 2

где 2 - суммарное количество искомых слов.

да, можно еще упростить:

 

SELECT `id` FROM `test` WHERE `word` IN ('foo', 'baz') GROUP BY `id` HAVING COUNT(*) = 2;  

P.S. да, @nikifalex уже давал выше почти такой же вариант. Змінено 21 липня 2016 користувачем sv2109

[markimax]

 

да, можно еще упростить:

 

SELECT `id` FROM `test` WHERE `word` IN ('foo', 'baz') GROUP BY `id` HAVING COUNT(*) = 2;  

 

HAVING тоже мега тормоз

 

Я выше писал - UNION еще можно использовать

Так как "пара" быстрых ПРОСТЫХ запросов объединенных могут быть во много раз быстрее  учитывая что таблица будет в кеше сервера

[markimax]

Когда то у меня была похожая задача (давно) и после тестирования на больших базах  я остановился на простых  запросах обьедененных UNION

LJ, HAVING,  COUNT - это мега тормоза

[ZeroHero]

да, можно еще упростить:

Нужно тестить, что будет быстрее на реальной базе. Вполне возможно, что COUNT в SELECT будет быстрее, чем COUNT в HEAVING.

SELECT COUNT(*) AS `count`, `id` FROM `test` WHERE `word` IN ('foo', 'bar') GROUP BY `id` HAVING `count` = 2

[sv2109]

UNION не спасает, потому что он просто объединяет выборки и, если я правильно понимаю, работает по логике "ИЛИ", запрос

SELECT t.id FROM `test` t WHERE t.word = 'bar'
UNION
SELECT t.id FROM `test` t WHERE t.word = 'foo' 
GROUP BY t.id

находит 1,2,3, как и запросы с "OR" или "IN()"

[sv2109]

Подведу итоги. 

 

Проверил на реальной базе с 42 тыс. записей все варианты запросов. 

Запросы проверял с "SQL_NO_CACHE", каждый выполнял около десяти раз, брал наименьшее и наибольшее значение, после чего считал среднее. 

 

Самыми быстрыми оказались 2 запроса 

 

1. с логикой "OR", оно и не удивительно, но этот запрос не подходит под задание, так как нужна логика "AND"

2. запрос с "HAVING COUNT(*) = 2", который практически вообще не отличается по скорости от обычного запроса с "OR"

Поэтому все возражения, о том, что и "HAVING" и "COUNT (*)" являются ну просто очень медленными оказались вообще безосновательными по крайней мене для данной задачи. Наверное потому, что MySQL очень умная, и она не считает "COUNT(*) " для всех записей, а только для найденных согласно других условий, а из 42 тыс. записей найтись могут напр. 50 (или даже меньше), а посчитать к-во для даже 50-ти записей для базы дело тысячной доли секунды, поэтому результат практически не отличается от обычного запроса с "OR"

 

3. Процентов на 20 медленнее от п. 1-2 работает запрос с вложенным подзапросом

 

4. Раза в 2 медленнее  от п. 1-2  работает запрос с джойнами

 

5. Запрос с UNION не проверял, так как так и не понял как с помощью него можно решить задачу.