CODEWITHSUNDEEP
mysqldatabase
boisterouslobster
boisterouslobster

Reputation: 1293

Nested Query Optimization

SELECT id
FROM `events` 
WHERE heading LIKE '%somestr%'
AND guests_since_start > 10
AND (SELECT COUNT(*)
FROM `submissions`
WHERE `submissions`.event_id = `events`.id)!=0;

Currently this query crashes my server. The idea is to get all id's from the events table that have corresponding rows in the submissions table (the events and submissions are linked by an event_it. As long as the number of rows is not 0, then I would like those records to be displayed.

The problem is the events table has approximately 3,000 records, while the submissions table has approximately 290,000.

Any help on the matter is appreciated.

Upvotes: 0

Views: 28

Answers (2)

Arth
Arth

Reputation: 13110

First I'd rewrite the query as:

   SELECT e.id
     FROM events e
    WHERE e.heading LIKE '%somestr%'
      AND e.guests_since_start > 10
      AND EXISTS (SELECT 1 FROM submissions s WHERE s.event_id = e.id);

OR as Matt S pointed out (I believe his may be slightly faster, but I prefer the reduced syntax):

   SELECT DISTINCT e.id
     FROM events e
     JOIN submissions s
       ON s.event_id = e.id
    WHERE e.heading LIKE '%somestr%'
      AND e.guests_since_start > 10

You were doing an unnecessary count on submissions.

I'd also make sure I had indexes covering (e.id, e.guests_since_start) and (s.event_id).

Upvotes: 1

Matt S
Matt S

Reputation: 15374

A direct join will filter it for you and be more efficient:

SELECT DISTINCT id
FROM `events` 
    INNER JOIN `submissions` 
    ON `submissions`.event_id = `events`.id
WHERE heading LIKE '%somestr%'
AND guests_since_start > 10

DISTINCT is required to filter out duplicates. This reduces the efficiency a bit (since MySQL now now has to remove dups), but overall it'll be much faster than any subquery. Indexes on submissions.event_id and events.id will help further.

Upvotes: 5

Related Questions