MySQL – How to Select Records Not Associated with a Specific Tag in Many-to-Many Relation

MySQLperformance

as shown in this sql-fiddle I have a bridge table events_tags representing a many-to-many between events and tags.

I need the fastest way to find all events not associated to a given tag t.

The first query in the fiddle shows the incorrect solution where a an event assigned to t and another tag is included in the result set.

The second query makes a anti-join (learned this from an answer by ypercubeᵀᴹ) and is correct, but very slow in my real schema, where it would return 18K rows from the bridge table in ~130ms on the hardware on which our application runs.

I need this functionality in an endpoint of a RESTful API, whose total response time must stay <300ms.

Is there a faster approach I can use or am I limited to the fact that the "real" query has to deal with a derived table of 18K rows?

EDIT
I failed to mention that I will then combine this (as a subquery) with another query selecting only events matching some criteria.
I am not selecting 18K events 🙂

P.S.: let me know if any other detail on the problem is required

Thanks

Best Answer

SELECT DISTINCT events.id, events.title
  FROM events 
  JOIN events_tags et1 
         on events.id = et1.event_id 
        and not exists ( select 1 
                           from events_tags et2 
                          where et2.event_id = et1.event_id
                            and et2.tag_id IN (1) );

And why a iden PK in events_tags? Drop that.
Just have a composite PK of tag_ID, event_id in that order.

This might be faster but I doubt it 

SELECT DISTINCT events.id, events.title
  FROM events 
  JOIN events_tags et1 
         on et1.event_id = events.id  
        and et1.tag_id not IN (1)
        and not exists ( select 1 
                           from events_tags et2 
                          where et2.event_id = et1.event_id
                            and et2.tag_id IN (1)
                       );

I don't think MYSQL supports except 

SELECT events.id, events.title
  FROM events 
  JOIN (   select event_id  
           from events_tags
           where tag_id not IN (1)
         except
           select event_id  
           from events_tags
           where tag_id     IN (1)
       ) tt
    on tt.event_id = events.id
Related Question