Mysql – Slow execution of a subquery with 0 matches

MySQLsubquery

In MySQL, I have two basic tables – Posts and Followers:

CREATE TABLE Posts (
  id int(11) NOT NULL AUTO_INCREMENT,
  posted int(11) NOT NULL,
  body varchar(512) NOT NULL,
  authorId int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY posted (posted),
  KEY authorId (authorId,posted)
) ENGINE=InnoDB;

CREATE TABLE Followers (
  userId int(11) NOT NULL,
  followerId int(11) NOT NULL,
  PRIMARY KEY (userId,followerId),
  KEY followerId (followerId)
) ENGINE=InnoDB;

I have the following query, which seems to be optimized enough (based on the EXPLAIN output):

  SELECT p.*
    FROM Posts p
   WHERE p.authorId IN (SELECT f.userId
                          FROM Followers f
                         WHERE f.followerId = 9
                      ORDER BY authorId)
ORDER BY posted
   LIMIT 0, 20

When followerId is a valid id (meaning, it actually exists in both tables), the query execution is almost immediate. However, when the id is not present in the tables, the query only returns results (empty set) after a 7 second delay.

Why is this happening? Is there some way to speed up this query for cases where there are no matches (without having to do a check ahead of time)?

Best Answer

The ORDER BY inside the subquery is rather useless if you are looking for userid.

You should do two things

First rewrite the query (remove the ORDER BY authorId)

SELECT p.*
    FROM Posts p
   WHERE p.authorId IN (SELECT f.userId
                          FROM Followers f
                         WHERE f.followerId = 9
                      )
ORDER BY posted
   LIMIT 0, 20;

Then, add this index

ALTER TABLE followers ADD INDEX followerId_userId_index (followerId,userId);

This should speed things up, even for empty set results.

Give it a Try !!!

You can also do the query using INNER JOIN

SELECT p.*
   FROM (SELECT userId FROM Followers WHERE followerId = 9) f
   INNER JOIN Posts p ON f.userId = p.authorId
ORDER BY p.posted
LIMIT 0, 20;
Related Question