EXPLAIN EXTENDED

From Stack Overflow:

I built a custom forum for my site using MySQL.

The listing page is essentially a table with the following columns: Topic, Last Updated, and Replies.

The DB table has the following columns:

id	name	body	date	topic_id	email

A topic has the topic_id of 0, and replies have the topic_id of their parent topic.

How do I query 20 last updated topics efficiently?

We have two kinds of topics here: those that had been answered and those that had not.

The queries for these two kinds of topics should be optimized using different methods and it's better to query for them using two different queries merged later using UNION ALL:

• To find last 20 replies, we will employ the technique described in this article:
  • Latest DISTINCT records: efficient subquery

  This technique uses a composite index on (date, id) to find 20 latest replies to distinct topic_id's:

  1. First, it scans the index on (date, id) to retrieve records with a non-zero topic_id in descending date order.
  2. For each record found on step 1, it scans the index on (date, id) again in descending order and finds the first record with the same topic_id.
  3. If the id's of records found on steps 1 and 2 match, this means that the record found on step 1 is the latest for this topic_id and we should return it.
• To find last 20 unreplied topics, we just use a good old NOT EXISTS.

  In this case a composite index on (topic_id, date) will come handy: we will need to select the last 20 unreplied topics, and this index will be useful to implement both filtering on topic_id and ordering by date DESC.

• Finally, we will merge these resultsets using UNION ALL and limit the result.

Now, let's create a sample table:
Read the rest of this entry »