Перейти к содержанию

Рекомендуемые сообщения

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

 

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



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"

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

 

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

 

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

 

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;
)
  • +1 1

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

Еще так можно

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

`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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

мимо :)

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

`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)

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

 

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

...

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

Изменено пользователем nikifalex
  • +1 1

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

Как-то так.

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 `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'

 

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

Как-то так.

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 будет искать по каждой подключенной таблице отдельно. 
 
Поэтому я и создал эту тему в надежде что может есть красивое решение, которого я не вижу. Как-то сразу не поверил, что для такой, казалось бы простой задачи, нету простого и быстрого решения. Но, увы :(
 
В любом случае, всем спасибо за потраченное время, оно не потрачено напрасно, мы все узнали что-то новое, получили новые знания, которые нам пригодятся в будущем. 
 
Тема открыта - если у кого-то будут новые идеи - пишите. 

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
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 - суммарное количество искомых слов.

  • +1 1

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
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 - суммарное количество искомых слов.

 

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

Тоже вариант

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

 

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

 

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
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 уже давал выше почти такой же вариант. Изменено пользователем sv2109

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

 

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

 

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

 

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

 

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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()"

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
Подведу итоги. 

 

Проверил на реальной базе с 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 не проверял, так как так и не понял как с помощью него можно решить задачу. 

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

Для публикации сообщений создайте учётную запись или авторизуйтесь

Вы должны быть пользователем, чтобы оставить комментарий

Создать учетную запись

Зарегистрируйте новую учётную запись в нашем сообществе. Это очень просто!

Регистрация нового пользователя

Войти

Уже есть аккаунт? Войти в систему.

Войти

  • Последние посетители   0 пользователей онлайн

    Ни одного зарегистрированного пользователя не просматривает данную страницу

×

Важная информация

На нашем сайте используются файлы cookie и происходит обработка некоторых персональных данных пользователей, чтобы улучшить пользовательский интерфейс. Чтобы узнать для чего и какие персональные данные мы обрабатываем перейдите по ссылке. Если Вы нажмете «Я даю согласие», это означает, что Вы понимаете и принимаете все условия, указанные в этом Уведомлении о Конфиденциальности.