LIMIT the return of non-unique values.

Question

I have two tables. Posts and Replies. Think of posts as a blog entry while replies are the comments.

I want to display X number of posts and then the latest three comments for each of the posts.

My replies has a foreign key "post_id" which matches the "id" of every post.

I am trying to create a main page that has something along the lines of

Post --Reply --Reply --Reply

Post --Reply

so on and so fourth. I can accomplish this by using a for loop in my template and discarding the unneeded replies but I hate grabbing data from a db I won't use. Any ideas?

Answer 1

Sounds like you just want the LIMIT clause for a SELECT statement:

SELECT comment_text, other_stuff FROM comments WHERE post_id = POSTID ORDER BY comment_time DESC LIMIT 3;

You'll have to run this query once per post you want to show comments for. There are a few ways to get around that, if you're willing to sacrifice maintainability and your sanity in the Quest for Ultimate Performance:

1. As above, one query per post to retrieve comments. Simple, but probably not all that fast.

2. Retrieve a list of post_ids that you want to show comments for, then retrieve all comments for those posts, and filter them client-side (or you could do it server-side if you had windowing functions, I think, though those aren't in MySQL). Simple on the server side, but the client-side filtering will be ugly, and you're still moving a lot of data from server to client, so this probably won't be all that fast either.

3. As #1, but use an unholy UNION ALL of as many queries as you have posts to display, so you're running one abominable query instead of N small ones. Ugly, but it'll be faster than options 1 or 2. You'll still have to do a bit of filtering client-side, but careful writing of the UNION will make that much easier than the filtering required for #2, and no wasted data will be sent over the wire. It'll make for an ugly query, though.

4. Join the posts and comments table, partially pivoting the comments. This is pretty clean if you only need one comment, but if you want three it'll get messy quickly. Great on the client side, but even worse SQL than #3, and probably harder for the server, to boot.

At the end of the day, I'd go with option 1, the simple query above, and not worry about the overhead of doing it once per post. If you only needed one comment, then the join option might be acceptable, but you want three and that rules it out. If windowing functions ever get added to MySQL (they're in release 8.4 of PostgreSQL), option 2 might become palatable or even preferable. Until that day, though, just pick the simple, easy-to-understand query.

Answer 2

Although there may be a clever way to get this in one query with no schema changes I'm guessing it wouldn't be performant anyway. Edit: Looks like tpdi has the clever solution. It looks potentially pretty fast, but I'd be curious to see a benchmark on specific databases.

Given the constraints of high performance and minimal data transfer I have two suggestions.

Solution with no schema changes or maintenance

First:

SELECT * FROM Posts

Collect the ids, then:

SELECT id FROM Replies WHERE post_id IN (?) ORDER BY id DESC

Finally, loop through those ids, grabbing only the first 3 for each post_id, then do:

SELECT * FROM Replies WHERE post_id IN (?)

More efficient solution if you are willing to maintain a few cache columns

The second solution is assuming that there are far more reads than writes, you can minimize lookups by storing the last three comment ids on the Posts table every time you add a Reply. In that case you would simply add three columns last_reply_id, second_reply_id, third_reply_id or some such. Then you can look up with either two queries like:

SELECT * FROM Posts

Collect the ids from those fields, then:

SELECT * FROM Replies WHERE post_id IN (?)

If you have those fields you could also manually construct a triple join, which would get the data in one query, although the field list would be quite verbose. Something like

SELECT posts.*, r1.title, r2.title ... FROM Posts 
  LEFT JOIN Replies as r1 ON Posts.last_reply_id = Replies.id
  LEFT JOIN Replies as r2 ON Posts.second_reply_id = Replies.id
  ...

Which you prefer probably depends on your ORM or language.

Answer 3

This is actually a pretty interesting question.

HA HA DISREGARD THIS, I SUCK

On edit: this answer works, but on MySQL it becomes tediously slow when the number of parent rows is as few as 100. However, see below for a performant fix.

Obviously, you can run this query once per post: select * from comments where id = $id limit 3 That creates a lot of overhead, as you end up doing one database query per post, the dreaded N+1 queries.

If you want to get all posts at once (or some subset with a where) the following will surprisingly work. It assumes that comments have a monotonically increasing id (as a datetime is not guaranteed to be unique), but allows for comment ids to be interleaved among posts.

Since an auto_increment id column is monotonically increasing, if comment has an id, you're all set.

First, create this view. In the view, I call post parent and comment child:

create view parent_top_3_children as
select a.*, 
(select max(id) from child where parent_id = a.id) as maxid, 
(select max(id) from child where id <  maxid 
  and parent_id = a.id) as maxidm1, 
(select max(id) from child where id < maxidm1 
  and parent_id = a.id) as maxidm2 
from parent a;

maxidm1 is just "max id minus 1"; maxidm2, "max id minus 2" -- that is, the second and third greatest child ids within a particular parent id.

Then join the view to whatever you need from the comment (I'll call that text):

select a.*, 
b.text as latest_comment,
c.text as second_latest_comment,
d.text as third_latest_comment
from parent_top_3_children a
left outer join child b on (b.id = a.maxid)
left outer join child c on (c.id = a.maxidm1)
left outer join child d on (c.id = a.maxidm2);

Naturally, you can add whatever where clause you want to that, to limit the posts: where a.category = 'foo' or whatever.

---

Here's what my tables look like:

mysql> select * from parent;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |    1 |    1 | NULL |
|  2 |    2 |    2 | NULL |
|  3 |    3 |    3 | NULL |
+----+------+------+------+
3 rows in set (0.00 sec)

And a portion of child. Parent 1 has noo children:

mysql> select * from child;
+----+-----------+------+------+------+------+
| id | parent_id | a    | b    | c    | d    |
+----+-----------+------+------+------+------+

. . . .
| 18 |         3 | NULL | NULL | NULL | NULL |
| 19 |         2 | NULL | NULL | NULL | NULL |
| 20 |         2 | NULL | NULL | NULL | NULL |
| 21 |         3 | NULL | NULL | NULL | NULL |
| 22 |         2 | NULL | NULL | NULL | NULL |
| 23 |         2 | NULL | NULL | NULL | NULL |
| 24 |         3 | NULL | NULL | NULL | NULL |
| 25 |         2 | NULL | NULL | NULL | NULL |
+----+-----------+------+------+------+------+
24 rows in set (0.00 sec)

And the view gives us this:

mysql> select * from parent_top_3;
+----+------+------+------+-------+---------+---------+
| id | a    | b    | c    | maxid | maxidm1 | maxidm2 |
+----+------+------+------+-------+---------+---------+
|  1 |    1 |    1 | NULL |  NULL |    NULL |    NULL |
|  2 |    2 |    2 | NULL |    25 |      23 |      22 |
|  3 |    3 |    3 | NULL |    24 |      21 |      18 |
+----+------+------+------+-------+---------+---------+
3 rows in set (0.21 sec)

The explain plan for the view is only slightly hairy:

mysql> explain select * from parent_top_3;
+----+--------------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
|  2 | DERIVED            | a          | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
|  5 | DEPENDENT SUBQUERY | child      | ALL  | PRIMARY       | NULL | NULL    | NULL |   24 | Using where |
|  4 | DEPENDENT SUBQUERY | child      | ALL  | PRIMARY       | NULL | NULL    | NULL |   24 | Using where |
|  3 | DEPENDENT SUBQUERY | child      | ALL  | NULL          | NULL | NULL    | NULL |   24 | Using where |
+----+--------------------+------------+------+---------------+------+---------+------+------+-------------+

However, if we add an index for parent_fks,it gets a better:

mysql> create index pid on child(parent_id);

mysql> explain select * from parent_top_3;
+----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+
|  1 | PRIMARY            | <derived2> | ALL  | NULL          | NULL | NULL    | NULL      |    3 |             |
|  2 | DERIVED            | a          | ALL  | NULL          | NULL | NULL    | NULL      |    3 |             |
|  5 | DEPENDENT SUBQUERY | child      | ref  | PRIMARY,pid   | pid  | 5       | util.a.id |    2 | Using where |
|  4 | DEPENDENT SUBQUERY | child      | ref  | PRIMARY,pid   | pid  | 5       | util.a.id |    2 | Using where |
|  3 | DEPENDENT SUBQUERY | child      | ref  | pid           | pid  | 5       | util.a.id |    2 | Using where |
+----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+
5 rows in set (0.04 sec)

---

As noted above, this begins to fall apart when the number of parent rows is few as 100, even if we index into parent using its primary key:

mysql> select * from parent_top_3 where  id < 10;
+----+------+------+------+-------+---------+---------+
| id | a    | b    | c    | maxid | maxidm1 | maxidm2 |
+----+------+------+------+-------+---------+---------+
|  1 |    1 |    1 | NULL |  NULL |    NULL |    NULL |
|  2 |    2 |    2 | NULL |    25 |      23 |      22 |
|  3 |    3 |    3 | NULL |    24 |      21 |      18 |
|  4 | NULL |    1 | NULL |    65 |      64 |      63 |
|  5 | NULL |    2 | NULL |    73 |      72 |      71 |
|  6 | NULL |    3 | NULL |   113 |     112 |     111 |
|  7 | NULL |    1 | NULL |   209 |     208 |     207 |
|  8 | NULL |    2 | NULL |   401 |     400 |     399 |
|  9 | NULL |    3 | NULL |   785 |     784 |     783 |
+----+------+------+------+-------+---------+---------+
9 rows in set (1 min 3.11 sec)

(Note that I intentionally test on a slow machine, with data saved on a slow flash disk.)

Here's the explain, looking for exactly one id (and the first one, at that):

mysql> explain select * from parent_top_3 where id = 1;
+----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+
|  1 | PRIMARY            | <derived2> | ALL  | NULL          | NULL | NULL    | NULL      | 1000 | Using where |
|  2 | DERIVED            | a          | ALL  | NULL          | NULL | NULL    | NULL      | 1000 |             |
|  5 | DEPENDENT SUBQUERY | child      | ref  | PRIMARY,pid   | pid  | 5       | util.a.id |  179 | Using where |
|  4 | DEPENDENT SUBQUERY | child      | ref  | PRIMARY,pid   | pid  | 5       | util.a.id |  179 | Using where |
|  3 | DEPENDENT SUBQUERY | child      | ref  | pid           | pid  | 5       | util.a.id |  179 | Using where |
 +----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+
 5 rows in set (56.01 sec)

Over 56 seconds for one row, even on my slow machine, is two orders of magnitude unacceptable.

So can we save this query? It works, it's just too slow.

Here's the explain plan for the modified query. It looks as bad or worse:

mysql> explain select * from parent_top_3a where id = 1;
+----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+
|  1 | PRIMARY            | <derived2> | ALL  | NULL          | NULL | NULL    | NULL      |  100 | Using where |
|  2 | DERIVED            | <derived4> | ALL  | NULL          | NULL | NULL    | NULL      |  100 |             |
|  4 | DERIVED            | <derived6> | ALL  | NULL          | NULL | NULL    | NULL      |  100 |             |
|  6 | DERIVED            | a          | ALL  | NULL          | NULL | NULL    | NULL      |  100 |             |
|  7 | DEPENDENT SUBQUERY | child      | ref  | pid           | pid  | 5       | util.a.id |  179 | Using where |
|  5 | DEPENDENT SUBQUERY | child      | ref  | PRIMARY,pid   | pid  | 5       | a.id      |  179 | Using where |
|  3 | DEPENDENT SUBQUERY | child      | ref  | PRIMARY,pid   | pid  | 5       | a.id      |  179 | Using where |
+----+--------------------+------------+------+---------------+------+---------+-----------+------+-------------+
7 rows in set (0.05 sec)

But it completes three orders of magnitude faster, in 1/20th of a second!

How do we get to the much speedier parent_top_3a? We create three views, each one dependent on the previous one:

create view parent_top_1 as  
select a.*, 
(select max(id) from child where parent_id = a.id) 
 as maxid 
from parent a;

create view parent_top_2 as  
select a.*, 
(select max(id) from child where parent_id = a.id and id < a.maxid) 
 as maxidm1 
from parent_top_1 a;

create view parent_top_3a as  
select a.*, 
(select max(id) from child where parent_id = a.id and id < a.maxidm1)
 as maxidm2 
from parent_top_2 a;

Not only does this work much more quickly, it's legal on RDBMSes other than MySQL.

Let's increase the number of parent rows to 12800, the number of child rows to 1536 (most blog posts don't get comments, right? ;) )

mysql> select * from parent_top_3a where id >= 20 and id < 40;
+----+------+------+------+-------+---------+---------+
| id | a    | b    | c    | maxid | maxidm1 | maxidm2 |
+----+------+------+------+-------+---------+---------+
| 39 | NULL |    2 | NULL |  NULL |    NULL |    NULL |
| 38 | NULL |    1 | NULL |  NULL |    NULL |    NULL |
| 37 | NULL |    3 | NULL |  NULL |    NULL |    NULL |
| 36 | NULL |    2 | NULL |  NULL |    NULL |    NULL |
| 35 | NULL |    1 | NULL |  NULL |    NULL |    NULL |
| 34 | NULL |    3 | NULL |  NULL |    NULL |    NULL |
| 33 | NULL |    2 | NULL |  NULL |    NULL |    NULL |
| 32 | NULL |    1 | NULL |  NULL |    NULL |    NULL |
| 31 | NULL |    3 | NULL |  NULL |    NULL |    NULL |
| 30 | NULL |    2 | NULL |  1537 |    1536 |    1535 |
| 29 | NULL |    1 | NULL |  1529 |    1528 |    1527 |
| 28 | NULL |    3 | NULL |  1513 |    1512 |    1511 |
| 27 | NULL |    2 | NULL |  1505 |    1504 |    1503 |
| 26 | NULL |    1 | NULL |  1481 |    1480 |    1479 |
| 25 | NULL |    3 | NULL |  1457 |    1456 |    1455 |
| 24 | NULL |    2 | NULL |  1425 |    1424 |    1423 |
| 23 | NULL |    1 | NULL |  1377 |    1376 |    1375 |
| 22 | NULL |    3 | NULL |  1329 |    1328 |    1327 |
| 21 | NULL |    2 | NULL |  1281 |    1280 |    1279 |
| 20 | NULL |    1 | NULL |  1225 |    1224 |    1223 |
+----+------+------+------+-------+---------+---------+
20 rows in set (1.01 sec)

Note that these timings are for MyIsam tables; I'll leave it to someone else to do timings on Innodb.

---

But using Postgresql, on a similar but not identical data set, we get similar timings on where predicates involving parent's columns:

 postgres=# select (select count(*) from parent) as parent_count, (select count(*) 
from child) as child_count;
 parent_count | child_count
--------------+-------------
        12289 |        1536

postgres=# select * from parent_top_3a where id >= 20 and id < 40;
 id | a | b  | c | maxid | maxidm1 | maxidm2
----+---+----+---+-------+---------+---------
 20 |   | 18 |   |  1464 |    1462 |    1461
 21 |   | 88 |   |  1463 |    1460 |    1457
 22 |   | 72 |   |  1488 |    1486 |    1485
 23 |   | 13 |   |  1512 |    1510 |    1509
 24 |   | 49 |   |  1560 |    1558 |    1557
 25 |   | 92 |   |  1559 |    1556 |    1553
 26 |   | 45 |   |  1584 |    1582 |    1581
 27 |   | 37 |   |  1608 |    1606 |    1605
 28 |   | 96 |   |  1607 |    1604 |    1601
 29 |   | 90 |   |  1632 |    1630 |    1629
 30 |   | 53 |   |  1631 |    1628 |    1625
 31 |   | 57 |   |       |         |
 32 |   | 64 |   |       |         |
 33 |   | 79 |   |       |         |
 34 |   | 37 |   |       |         |
 35 |   | 60 |   |       |         |
 36 |   | 75 |   |       |         |
 37 |   | 34 |   |       |         |
 38 |   | 87 |   |       |         |
 39 |   | 43 |   |       |         |
(20 rows)

Time: 91.139 ms