Indexing SET field

Question

I have two entities A and B. They are related with many to many relation. Entity A can be related up to 100 B entities. Entity B can be related up to 10000 A entities. I need quick way to select for example 30 A entities, that have relation with specified B entities, filtered and sorted by different attributes.

Here how I see ideal solution: I put all information I know about A entities, including their relations with B entities into single row (Special table with SET field) then add all necessary indexes. The problem is that you can't use index while querying by SET field. What should I do? I can replace database with something different, if that'll help.

UPDATE: I'm sorry. Looks like I've forgotten to mention one important detail. I need to find those A entries that have relations with B entry with id = 1 and with B entry with id = 2 at the same time. So if using joins I'll have something similar to:

SELECT a.id, count(*) as cnt FROM a INNER JOIN ab ON a.id = ab.a_id WHERE ab.b_id IN(1,2) GROUP BY a.id ORDER BY NULL having cnt = 2

Which gives me very bad perfomance

Answer 1

Why don't you just do this:

SELECT  *
FROM    a
WHERE   a.id IN
        (
        SELECT  ab.a
        FROM    b
        JOIN    ab
        ON      ab.b = b.id
        WHERE   b.id IN (1, 2, 3, 4)
        )

and create a PRIMARY KEY on ab (b, a)?

Update:

Use this:

SELECT  *
FROM    a
WHERE   (
        SELECT  COUNT(*)
        FROM    ab
        WHERE   ab.a = a.id
                AND ab.b IN (1, 2, 3, 4)
        ) = 4
ORDER BY
        ...
LIMIT 30

or this:

SELECT  a.*
FROM    (
        SELECT  a
        FROM    ab
        WHERE   ab.b IN (1, 2, 3, 4)
        GROUP BY
                a
        HAVING  COUNT(*) = 4
        ) q
JOIN    a
ON      a.id = q.id
ORDER BY
        ...
LIMIT 30

You'll need to have a PRIMARY KEY on ab (b, a) (in this order) for this to work fast.

Which query is more efficient depends on your data distribution.