'Better' Method for matching ID sets in MySQL statement

Question

I'm currently running some SQL which uses an IN expression to match against multiple IDs. However, I would ideally like to be able to match up certain IDs with others so they must appear together to return a result. Here's an example:

Edit: The IDs I'm matching are a part of a many-to-many relationship. The structure is like this:

Articles ArticleKeywords Keywords

An article can have multiple Keywords linked to it by the ArticleKeywords table. I am currently using the IN expression to match any of the Keyword IDs against the records. However, I'd like to match certain records against small groups of keywords i.e. a keyword must appear with another keyword for a record to be matched.

Current: ... AND id IN ('25', '566', '156', '166', '7345')

More specific: ... AND ((id = '25' AND id = '566') OR (id = '156' AND id = '166') OR (id = '7345'))

Although the second option might work, I'm thinking it probably won't be very performance savvy. Is there another way this can be done, or should I be going about it another way?

Thanks for your help.

Answer 1

Well your second option will never work...

((id = '25' AND id = '566') --For this to return the column `id` would have to = both 25 & 566 which it obviously can't
OR 
(id = '156' AND id = '166') --For this to return the column `id` would have to = both 156 & 166 which it obviously can't
OR 
(id = '7345'))

What exactly are you trying to achieve... What do you mean by "match up certain IDs with others so they must appear together" Do you mean on consecutive rows ?

Answer 2

Edited based on comments. Say you're searching for articles that:

• Have a keyword named 'a1'
• Or, have both keywoards named 'b1' and 'b2'
• Or, have either keyword 'c1', 'c2' or 'c3'

You can query like:

select a.id
from Articles a 
inner join ArticleKeywords ak on ak.articleid = a.id
inner join Keywords k on k.id = ak.keywordid
group by a.id
having 
    sum(case when k.name in ('a1') then 1 else 0 end) = 1
    or sum(case when k.name in ('b1','b2') then 1 else 0 end) = 2 
    or sum(case when k.name in ('c1','c2,'c3') then 1 else 0 end) > 0

Per SquareCog's comment, you can greatly increase performance with an early WHERE clause. The clause would limit the grouping to the relevant keywords only. In the above query, add the WHERE just before the HAVING:

...
inner join Keywords k on k.id = ak.keywordid
where k.name in ('a1','b1','b2','c1','c2','c3')
group by a.id
...

You can retrieve the other details of the article(s) like:

select *
from Articles
where id in (
    ...query from above here...
)

Say you have a table that contains groups to search for, defined like:

groupid - keywordid
1 - 1
1 - 2
2 - 3

Meaning the article has to match ((keyword 1 and keyword2) or keyword3). Then you can query like this:

select ak.articleid
from ArticleKeywords ak
inner join Search s on ak.keywordid = s.keywordid
group by s.searchgroup, ak.articleid
having count(*) = (
    select count(*) from #Search s2 where s2.Searchgroup = s.SearchGroup
)

Answer 3

The answer given by Andomar using GROUP BY and HAVING is a common way to solve this kind of problem, but it doesn't perform very well. The GROUP BY often causes a temporary table.

Back to your example:

...AND ((id = '25' AND id = '566') OR (id = '156' AND id = '166') OR (id = '7345'))

This can never be true. The conditions in a WHERE clause apply to one row at a time. The id column can never have two values on a given row. I understand conceptually what you are intending to test for, but this isn't the way SQL works.

When you need to write a condition that involves values occurring on multiple rows, another solution is to use a self-join.

SELECT *
FROM ArticleKeywords k1
 LEFT OUTER JOIN ArticleKeywords k2 ON (k1.article_id = k2.article_id)
WHERE k1.keyword_id = '7345'
 OR (k1.keyword_id = '25' AND k2.keyword_id = '566')
 OR (k1.keyword_id = '156' AND k2.keyword_id = '166');

Most people would call k1 and k2 "table aliases." But if you think of these aliases as pointing to individual rows in the table, instead, then it becomes clearer how to write conditions using self-joins.

Of course if you need to test for a small group of three values instead of two values, you need to do another self-join.

This solution may not be able to use indexes efficiently, but it won't incur the temporary table that the GROUP BY solution does. Try both solutions, use EXPLAIN to analyze them, and then measure their performance to compare.