SQL: как сделать правильно?

Может так?

SELECT t.* 
from tasks t 
join tags tg on tg.task_id=t.task_id 
where tg.tag in ('sometag','sometag2')

призываю kron73

http://www.linux.org.ru/forum/web-development/5167685

SELECT *
FROM tasks t
WHERE 0<(SELECT COUNT(*) FROM tags WHERE tag='sometag' AND task_id=t.task_id) AND 0<(SELECT COUNT(*) FROM tags WHERE tag='sometag2' AND task_id=t.task_id);

я бы сделал так

зачем ему join? Ему сами данные из tags не нужны

Все правильно. Убран кореллированный подзапрос и все. Какие ещё-то необходимы ТС хитрости?

Не знаю как на других системах, а на Oracle 10g вариант с джойном должен быть быстрее.

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

В принципе, вот еще способ убрать кореллированность без джойна [code] select * from tasks t where t.id in ( select task_id from tags where tag = 'AAA' or tag = 'BBB' ) [/code]

Для начала надо добавить ещё один индекс в таблицу «tags» по полю «tag»

select * from tasks
where task_id in
(select task_id
from tags
where tag in ('тэг1','тэг2',...и т.д.)
);

>(PRIMARY KEY (task_id, tag))

А зачем тебе составной первичный ключ? Переделай первичный на task_id и добавь уникальный ключ по полю tag.

а если вот так?

SELECT
  tasks.*
FROM
  tags
  LEFT JOIN tasks
    ON tags.task_id = tasks.id
WHERE
  MATCH(tags.tag_name) AGAINST ('tag1 tag3' IN BOOLEAN MODE)
GROUP BY tasks.task_id

Это перестанет соответствовать предметной области - у каждого таска может быть несколько тэгов.

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

Поэтому, скорее всего, логично выделить таблицу тэгов tags=(id, short_name, name), отдельно таблицу тасков tasks=(id,short_name,name,...) и таблицу, связывающую их tasks$tags=(task_id,tag_id).

Соответственно, измените запросы. Как разместить индексы - очевидно.

Я бы сделал так:

SELECT TS.task_id 
  FROM tasks TS, tags TG
  WHERE  TS.task_id = TG.task_id AND 
         TG.tag IN ('sometag', 'sometag2');

>у каждого таска может быть несколько тэгов.

Действительно, что-то я этот момент прохлопал.

select *
from tasks t
where
  exists (select 1 from tags where tag='sometag' and task_id=t.task_id) and
  exists (select 1 from tags where tag='sometag2' and task_id=t.task_id);

пожалуй, наиболее православное. потому что where id in (...) будет тормозить на больших объёмах.

кстати, обратите внимание, exists (...) and exists (...) не равно exists (... in(...)) =)

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

солюшн - отношение между задачами и тегами должно быть many to many. как следствие через промежуточную таблицу. а далее, выборку строить через связку этих трех таблиц и заданием критерия тегов (хоть по явному набору IDшников, хоть простым LIKE'ом)

вау аж 2 подзапроса, mysql сервер будет благодарен тебе

SELECT ..,count(tags.task_id) as cnt 
    FROM tasks 
         LEFT JOIN tags on tags.task_id = tasks.id
              WHERE task.name in ('tag1','tag2')
                 GROUP BY tasks.id
                    HAVING cnt = 2;

так же в tags сделать отдельные индексы, а не группированный, а лучше сделать таблицы:

tasks (id, ...), tags (id,name) и task_tag (id,task_id,tag_id)

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

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

Только почему просто JOIN, а не INNER JOIN?

Принципиальное отличие от изначального варианта? Все равно два подзапроса, выполняющих почти те же действия. Только в вашем случае они не извлекают сами данные, а считают количество строк (что есть, конечно, лучше, но в данном случае выгода очень и очень небольшая).

Тоже интересный вариант. Хоть и, если я правильно все понимаю, не отличается принципиально от варианта с джойном.

Тоже интересный вариант. Хоть и, если я правильно все понимаю, не отличается принципиально от варианта с джойном. Или я не прав?

А зачем тебе составной первичный ключ?

Почему это неправильно и как сделать правильно?

SELECT
  tasks.*
FROM
  tags
  LEFT JOIN tasks
    ON tags.task_id = tasks.id
WHERE
  MATCH(tags.tag_name) AGAINST ('tag1 tag3' IN BOOLEAN MODE)
GROUP BY tasks.task_id

А как оно работает и что оно делает?

соответсвенно таблица tags должна быть MYISAM и поле tag_name FULLTEXT INDEX

Не может, ибо task_id - FOREIGN KEY, который MYISAM не поддерживает (поэтому движок - InnoDB).

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

Поэтому, скорее всего, логично выделить таблицу тэгов tags=(id, short_name, name), отдельно таблицу тасков tasks=(id,short_name,name,...) и таблицу, связывающую их tasks$tags=(task_id,tag_id).

Интересный вариант. А не скажется ли это отрицательно на производительности? (ведь чтобы выбрать задачи по тэгу, имеющему имя name, придется выбрать его tag_id из tags, потом выбрать task_id из tasks$tags, потом выбрать инФу из tasks по полученному task_id, вменяемый запрос пока не придумал, все варианты очень монструозны и содержат кучу подзапросов).

mysql воспримет это как раз как inner join

а зачем crossjoin неявный делать? Это медленнее чем обычный join http://ru.wikipedia.org/wiki/Join_(SQL)#CROSS_JOIN

Спасибо за объяснение.

нслово неявный - лишнее

чтобы точно ответить на это надо использовать explain для анализа запроса, вроде как подзапрос в where медленнее

тут нужно использовать explain, смотреть всели индексы задействованы и т.д.

> а зачем crossjoin неявный делать? Это медленнее чем обычный join http://ru.wikipedia.org/wiki/Join_%28SQL%29#CROSS_JOIN

По идее хорошая СУБД должна распознать мой вариант как эквивалент JOIN - ведь соединение происходит по первичному-и-внешнему ключу. Но бывают и нехорошие СУБД, тут не поспоришь. И это, увы, не единственный мой косяк, товарищ vahvarh подтвердит.

Ну а кошернее всего будет реорганизовать данные по рецепту halturin-а. Действительно, у нас же отношение многие ко многим - чего тогда огород городить когда нужно сделать таблицу link_task_tag, и запрос будет совсем уж православным (и всяко более быстрым, чем предложенные):

SELECT TS.task_id
 FROM link_task_tag LTT
   INNER JOIN tasks TS  ON (TS.task_id = LTT.task_id)
   INNER JOIN tags  TG  ON (TG.tag_id  = LTT.tag_id)
 WHERE TG.tag='tag1' AND TG.tag='tag2';

Для других не знаю, но для oracle, например, лучше использовать именно = а не LIKE, - для скорости.

Ну и этот запрос должен быть полностью эквивалентен (в том числе и по скорости - на хорошей СУБД) вот такому:

SELECT TS.task_id
 FROM link_task_tag LTT, tasks TS, tags  TG
   WHERE (TS.task_id = LTT.task_id) AND
         (TG.tag_id  = LTT.tag_id ) AND
         (TG.tag='tag1' AND TG.tag='tag2');

+ DISTINCT

тут LIKE как раз то и не нужен, нужно точное соответствие.

Вроде как лишний join большее время обработки запроса, хотя в остальном согласен.

PS только не только вроде как топикстареру не только id надо вывести а все данные в таблице tasks

А, ну да, там надо сделать SELECT TS.* <...>;

Структуру базы сделал как вы советовали (пока структуру основной базы не менял, просто создал еще одну).

Запрос, на 100% эквивалентный вашему, дает пустой результат (при этом все таблицы заполнены корректно, и мой запрос с подзапросами дает правильный результат).

Вот сам запрос:

SELECT TS.* 
FROM tasks$tags TT
INNER JOIN tasks TS ON (TS.task_id = TT.task_id)
INNER JOIN tags TG ON (TG.tag_id=TT.tag_id)
WHERE TG.tag_name='tag1' AND TG.tag_name='tag2';

Содержимое таблиц:

mysql> SELECT * FROM tags;                                                                 +--------+----------+
| tag_id | tag_name |
+--------+----------+
|      1 | tag1     |
|      2 | tag2     |
|      3 | tag3     |
|      4 | tag4     |
|      5 | tag5     |
+--------+----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM tasks;
+---------+-------+
| task_id | name  |
+---------+-------+
|       1 | task1 |
|       2 | task2 |
+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tasks$tags;
+---------+--------+
| task_id | tag_id |
+---------+--------+
|       1 |      1 |
|       1 |      2 |
|       2 |      2 |
|       1 |      3 |
|       2 |      4 |
+---------+--------+
5 rows in set (0.00 sec)

SELECT T.* FROM task T WHERE T.task_id IN
(SELECT TS.task_id
 FROM link_task_tag LTT, task TS, tag  TG
   WHERE (TS.task_id = LTT.task_id) AND
         (TG.tag_id  = LTT.tag_id ) AND
         (TG.name IN ('tag1', 'tag2'))
         GROUP BY TS.task_id 
         HAVING COUNT(*) = 2);
-- HAVING COUNT(*) = 2 - столько же, сколько тегов в IN ('tag1', 'tag2')

//fixed

Накрутить сюда JOIN-тов предлагаю в качестве упражнения.

А зачем вы решили сначала выбрать одно, затем второе, затем третье? Вот так сделайте.

select t.* from task t, tag g, task$tag m where t.id = m.task_id and m.tag_id = g.id and g.name in ( 'TAG1', 'TAG2', ... )

Оптимизатор сам сориентируется, как сделать эту выборку оптимальнее. Она очень проста для его работы. Попытки соптимизировать это вручную не от большого ума. Траст ми :-)

Нет никаких причин, чтоб этот вариант выполнялся дольше варианта с соединением.

http://www.linux.org.ru/forum/job/5770324