Optimize LEFT JOIN on table with 30 000+ rows

Question

I have a website where visitors can leave comments. I want to add the ability to answer comments (i.e. nested comments).

At first this query was fast but after I populated the table with the existing comments (about 30000) a simple query like:

SELECT c.id, c2.id
  FROM (SELECT id
         FROM swb_comments
         WHERE pageId = 1411
         ORDER BY id DESC
         LIMIT 10) AS c
  LEFT JOIN swb_comments AS c2 ON c.id = c2.parentId

took over 2 seconds, with no childComments(!).

How do I optimize a query like this? On possible solution would be http://www.ferdychristant.com/blog//articles/DOMM-7QJPM7 (scroll to "The Flat Table Model done right") but this makes pagination rather difficult (how do I limit to 10 parent comments within 1 query?)

The table has 3 indexes, id, pageId and ParentId.

Thanks in advance!

EDIT:

Table definition added. This is the full definition with some differences to the above SELECT query, (i.e. pageId instead of numberId to avoid confussion)

CREATE TABLE `swb_comments` (
    `id` mediumint(9) NOT NULL auto_increment,
    `userId` mediumint(9) unsigned NOT NULL default '0',
    `numberId` mediumint(9) unsigned default NULL,
    `orgId` mediumint(9) unsigned default NULL,
    `author` varchar(100) default NULL,
    `email` varchar(255) NOT NULL,
    `message` text NOT NULL,
    `IP` varchar(40) NOT NULL,
    `timestamp` varchar(25) NOT NULL,
    `editedTimestamp` varchar(25) default NULL COMMENT 'last edited timestamp',
    `status` varchar(20) NOT NULL default 'publish',
    `parentId` mediumint(9) unsigned NOT NULL default '0',
    `locale` varchar(10) NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `userId` (`userId`),
    KEY `numberId` (`numberId`),
    KEY `orgId` (`orgId`),
    KEY `parentId` (`parentId`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34748 ;

Answer 1

The issue is that MySQL cannot apply index if it need to deal with a result from a derived query (that's why you have NULL in the possible_keys column). So I suggest to filter out ten comments that you need:

SELECT * FROM swb_comments WHERE pageId = 1411 ORDER BY id DESC LIMIT 10

And after that send separate request to get answers for each comment id:

SELECT * FROM swb_comments WHERE parentId IN ($commentId1, $commentId2, ..., $commentId10)

In this case database engine will be able to apply pageId and parentId indexes efficiently.