Rsa97: я знаю про EXPLAIN, но как-то не умею читать его выдачу. Он пишет про использование каких-то индексов, а я даже не всегда в курсе, откуда они берутся. Например, написано using index, а какой - не сказано. А их иногда несколько у таблицы. И вообще форма вывода там специфичная.
Кстати, и это тоже логично. В вашем варианте сначала скорее всего просматривается таблица тегов благодаря оптимизатору, а она очень мала (сейчас там меньше 10 строк). Дальше выбираются из первой таблицы уже нужные строки (для этого можно использовать частичный индекс в случае people и первичный ключ id в случае names). Отбирается их ровно столько, сколько строк в user_tags (если у нас нет дублирований во второй таблице). И выборка идёт по индексу. Дальше группировка и оценка числа элементов в группе идёт уже в маленькой временной таблице.
А то, что сотворил я, хоть и смотрится неплохо - это похоже fullscan по таблице people, в которой несколько тысяч строк как минимум, и для каждой итерации ещё не самый лёгкий подзапрос делается.
Я тут вспомнил, что такая таблица есть, и хранит она пары (id, имя) в единственном экземпляре (там нет записей с двумя одинаковыми id). Стоит наверное использовать её. Оказывается, JOIN работает быстрее чем вложенный запрос, во всяком случае в логе меньше операций при этом. Там весь JOIN занимал 0.0009 секунды, а тут только удаление временной таблицы занимает 0.006 мс.
А самое изящное на ваш взгляд решение было бы какое? Создать дублирующую таблицу, где будут только id людей из вк в качестве единственной колонки (с требованием уникальности)? Или просто оставить мой текущий вариант
SELECT DISTINCT `user_id` FROM `people` WHERE (SELECT COUNT(*) FROM `user_tags` WHERE `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2))=2
Rsa97: да, прошу прощения, я уже понял) Там потому что нет как таковой таблицы пользователей. А таблица people хранит пары (user_id, event_id) - вроде информации о посещении разных событий. Вот что значит неудачное название для таблицы выбрано...
Тогда да, DISTINCT только в помощь :)
Но это не объясняет, почему INNER JOIN выдавал пустой результат.
Rsa97: спасибо, не заметил. Теперь работает. Не очень правда понял, почему в случае с LEFT JOIN всё "сыпится". Кроме заполнения нулевыми значениями в документации особых оговорок нет про специфику LEFT/RIGHT JOIN вроде.
Такой ещё вопрос немного не по теме: почему при выполнении данного запроса я получаю три одинаковых строки, ведь по идее должна проходиться вся таблица `people`, и для каждой её строки должен вычисляться подзапрос (и в зависимости от его значения строка берётся в выдачу или не берётся). Откуда дублирование строк?
SELECT `user_id` FROM `people` WHERE (SELECT COUNT(*) FROM `user_tags` WHERE `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2))=2
А, чёрт, ну логично. Соотнесённый запрос выполняется для каждой найденной строки. На этапе анализа внешнего WHERE никакой `count` ещё не существует :)
Тогда логичнее оставить последний вариант. Хотя механизм работы третьего варианта я так и не до конца понял. Видимо, для HAVING всё равно с чем работать, и тут он просто создаёт одну большую группу и фильтрует её. Оставляя, конечно же, дубли строк.
Rsa97: смотрите, я немного поменял запрос, добавив вложенный запрос для наглядности происходящего, сути он вроде не меняет
SELECT `people`.`user_id`, (SELECT COUNT(*) FROM `user_tags` WHERE `tag_id` IN (1,2) AND `user_id`=`people`.`user_id`) AS `count` FROM `people` LEFT JOIN `user_tags` ON `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2) WHERE `tag_id` IS NOT NULL GROUP BY `user_tags`.`user_id` HAVING `count`=2 - не работает, пустой результат
SELECT `people`.`user_id`, (SELECT COUNT(*) FROM `user_tags` WHERE `tag_id` IN (1,2) AND `user_id`=`people`.`user_id`) AS `count` FROM `people` LEFT JOIN `user_tags` ON `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2) WHERE `tag_id` IS NOT NULL AND `count`=2 - не работает, пишет, что `count` является неизвестной колонкой
SELECT DISTINCT `people`.`user_id`, (SELECT COUNT(*) FROM `user_tags` WHERE `tag_id` IN (1,2) AND `user_id`=`people`.`user_id`) AS `count` FROM `people` LEFT JOIN `user_tags` ON `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2) WHERE `tag_id` IS NOT NULL HAVING `count`=2 - работает, хотя HAVING без GROUP BY - это что-то странное конечно
SELECT `people`.`user_id`, (SELECT COUNT(*) FROM `user_tags` WHERE `tag_id` IN (1,2) AND `user_id`=`people`.`user_id`) AS `count` FROM `people` LEFT JOIN `user_tags` ON `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2) WHERE `tag_id` IS NOT NULL GROUP BY `user_id` HAVING `count`=2 - тоже работает
Как вы это объясните? По логике то, что вы написали, должно было работать, но оно не хочет :)
Rsa97: хм, почему-то ваше решение работает только для одного тега. Я что-то делаю не так, или что-то мы не учли? К слову, даже для того чтобы работало с одним пришлось модифицировать запрос, добавив "WHERE `tag_id` IS NOT NULL", потому что при JOIN там где не находит - MySQL комбинирует строки со строками с нулевыми значениями в колонках, где условие не может быть выполнено.
Я кстати долго сейчас читал мануалы, и сам пришёл к тому, что написано у Вас) Правда, немного в конце допустил ошибку, использовав WHERE вместо HAVING, но такой вариант MySQL не принял, и выдал ошибку.
Одно только жаль - я сколько ни гуглил, вроде не нашёл способа посчитать длину списка динамически. Похоже, такое сделать невозможно. Было бы круто, если бы можно было, тогда от PHP требовался бы только список ID тегов, но не общее их число. Меньше теоретическая вероятность ошибок была бы.
Артём Каретников: не могли бы вы по шагам расписать, как это работает? Я вижу, тут перекрёстная ссылка: вложенный запрос ссылается на алиас из внешнего запроса. Но вложенный выполняется первым... Как в таком случае работает условие с алиасом?
Небольшой апдейт: считаем, что пары уникальны. Я вижу, это стало предметом недопонимания. В БД это не заложено, но это поддерживается бизнес-логикой, плодить одинаковые пары нет никакого смысла. Ну и да, можно создать индекс, который запретит добавление одинаковых пар.
Была ещё такая мысль - как-то посчитать количество найденных результатов через COUNT(*) и сравнить его с количеством переданных элементов (его можно статически вставить через PHP), но считать надо по каждому id_user... Возможно, что-то типа GROUP BY тут поможет. Но я такого никогда не делал, поэтому опять же, затруднюсь.
Александр Попов @popov654
Не очень понял... В вашем варианте во вложенном запросе будет куча неуникальных пар. Да, я понимаю, что это не принципиально, поскольку IN принимает список, а в списке неуникальные значения просто будут проигнорированы, грубо говоря. Спасибо за наводку. Но у меня главная сложность была с "И", а не с "ИЛИ". То есть мне нужно вернуть id пользователей, у которых встречаются теги с id 5 и 7. Значит вложенный запрос должен вернуть только те id, у которых есть две строки в таблице пар: (x, 5) и (x, 7), (y, 5) и (y, 7). Не важно, будут ли дубли, главное, что надо вернуть те айди, у которых есть две пары с заданными id тегов (или более), но не возвращать те, где всего одна из этих двух пар. Честно говоря, затрудняюсь сходу разгадать вашу загадку)
Судя по тому, что я читал про grid, он довольно сложен в понимании. Не так там всё интуитивно) flex-ы очень простые, но более старыми браузерами не поддерживаются увы.