CODEWITHSUNDEEP
mysqlperformanceinnodb
Dennis Hackethal
Dennis Hackethal

Reputation: 14275

Mysql queries mindnumbingly slow

I am having issues with an InnoDB mysql database. E.g., I have two tables, events and events_artists with the following architecture:

events:
id (PK)
host_id
date

events_artists:
id (PK)
event_id
artist_id

The table events has about 100,000 entries, events_artists holds about 150,000.

One of the things I have to do is check for entries in the table events that are not referred to in the table events_artists. To do that, in another post I have received the query

SELECT * FROM events WHERE id IS NULL OR id NOT IN (SELECT event_id FROM events_artists)

From the logic, this query looks good to me. However, it is just extremely slow. I let it run for one hour now, and still no result. Something has to be wrong there.

I am grateful for any help on how to optimize this!

Thanks

Charles

SOLUTION FOUND

The following query makes the statement much faster:

SELECT * 
FROM events a
     LEFT JOIN events_artists b
     ON a.id = b.event_id
     WHERE b.event_id IS NULL;

However, the main speed boost was adding foreign keys. I had to remove them in the interim for some duplicate search and added them back now, which increases speed a lot. While the query took more than an hour before, it only takes one second now.

Thank you!

Charles

Upvotes: 1

Views: 94

Answers (3)

Madhivanan
Madhivanan

Reputation: 13700

Try this

select 
    ev.* from events as ev 
    left join events_artists as ea on ev.id=ea.event_id
where 
    ea.event_id is null or ev.id is null

Upvotes: 1

Romain
Romain

Reputation: 12809

If you run an EXPLAIN of your query, you'll see the subquery is treated as DEPENDENT, which means it'll be run once for each row in your events table, hence it's slow.

You can change it to be independent like so:

SELECT events.*
FROM events
LEFT JOIN events_artists ON (events.id = events_artists.event_id)
WHERE events_artists.event_id IS NULL

Upvotes: 1

Omesh
Omesh

Reputation: 29061

To get faster results you should avoid using sub-query. Alternatively you can try using LEFT JOIN to get records which are in events but not in events_artists.

Visit Visual explanation of joins

SELECT * 
FROM events a
     LEFT JOIN events_artists b
         ON a.id = b.event_id
WHERE b.event_id IS NULL;

Also make sure that you have index on columns id and event_id for faster query execution.

Upvotes: 3

Related Questions