1

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

  • I think the main reason is ORDER BY authorID in subquery (ignoring the fact that order by is useless in this case as RolandoMySQLDBA pointed out) . Note: there is no Followers.authorId so it's resolved to Posts. authorId – a1ex07 Nov 13 '14 at 19:44
1

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;
  • Note that the subquery returns multiple rows - not just one. Following the steps you suggested doesn't improve things, unfortunately - it results in 'temporary' and 'filesort' on the Following table. – Melllvar Nov 13 '14 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.