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
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)?