LIMIT返回非唯一值 [英] LIMIT the return of non-unique values

问题描述

我有两个表。帖子和回复。将帖子视为博客条目，而回复是评论。

我想显示X个帖子，然后显示每个帖子的最新三个评论。 p>

我的回复有一个与每个帖子的id相匹配的外键post_id。

创建一个包含

行的主页。

Post
--Reply
--Reply
--Reply

Post
--Reply

等等。我可以通过在我的模板中使用一个for循环，并抛弃不必要的回复，但我讨厌从数据库中抓取数据我不会使用。任何想法？

解决方案

这实际上是一个非常有趣的问题。

HA HA DISREGARD THIS，I SUCK

编辑时：这个答案是有效的，但是在MySQL上，

显然，您可以每个帖子运行一次此查询： select * from comments where id = $ id limit 3 这会产生很多开销，因为你最终对每篇文章进行一次数据库查询，可怕的 N + / em>。

如果你想一次性获得所有帖子（或某个子集的位置），以下会令人惊讶地工作。它假定注释有一个单调增加的id（因为datetime不能保证是唯一的），但允许注释id在帖子之间交替。

由于auto_increment id列是单调增加的，如果注释有一个id，则您都已设置。

首先，创建此视图。在视图中，我调用post parent 并注释 child ：

 将视图parent_top_3_children创建为
选择a。*，
（从child中选择max（id）parent_id = a.id）作为maxid，
（从id 和parent_id = a.id的子节点中选择max（id））作为maxidm1，
（从id 的子节点中选择max parent_id = a.id）as maxidm2 
 from parent a; 
  

maxidm1 只是max id minus 1 ; maxidm2 ，max id minus 2 - 即特定父ID

中的第二个和第三个最大子id。

然后将视图加入到评论中所需的任何内容（我会称之为 text ）：

 选择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 children d on（c.id = a.maxidm2）; 
  

当然，你可以添加任何你想要的where子句，限制帖子： where a.category ='foo'或任何。

---

表格如下：

  mysql> select * from parent; 
 + ---- + ------ + ------ + ------ + 
 | id | a | b | c | 
 + ---- + ------ + ------ + ------ + 
 | 1 | 1 | 1 | NULL | 
 | 2 | 2 | 2 | NULL | 
 | 3 | 3 | 3 | NULL | 
 + ---- + ------ + ------ + ------ + 
集合中的3行（0.00秒）
  

和儿童的一部分。父级1有noo个子级：

  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行（0.00秒）
  

视图为我们提供： p>

  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行（0.21秒）
  

视图的解释计划只是稍微有点毛：

  mysql>解释select * from parent_top_3; 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ------ + ------ +  - ----------- + 
 | id | select_type |表|类型| possible_keys |键| key_len | ref |行|额外| 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ------ + ------ +  - ----------- + 
 | 1 | PRIMARY | < derived2> | ALL | NULL | NULL | NULL | NULL | 3 | | 
 | 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 3 | | 
 | 5 |相关子词|子| ALL | PRIMARY | NULL | NULL | NULL | 24 |使用where 
 | 4 |相关子词|子| ALL | PRIMARY | NULL | NULL | NULL | 24 |使用where 
 | 3 |相关子词|子| ALL | NULL | NULL | NULL | NULL | 24 |使用where 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ------ + ------ +  - ----------- + 
  

但是，如果我们为parent_fks添加索引，它得到更好：

  mysql>在子代（parent_id）上创建索引pid; 
 
 mysql>解释select * from parent_top_3; 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ----------- + ---- -  + ------------- + 
 | id | select_type |表|类型| possible_keys |键| key_len | ref |行|额外| 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ----------- + ---- -  + ------------- + 
 | 1 | PRIMARY | < derived2> | ALL | NULL | NULL | NULL | NULL | 3 | | 
 | 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 3 | | 
 | 5 |相关子词|子| ref | PRIMARY，pid | pid | 5 | util.a.id | 2 |使用where 
 | 4 |相关子词|子| ref | PRIMARY，pid | pid | 5 | util.a.id | 2 |使用where 
 | 3 |相关子词|子| ref | pid | pid | 5 | util.a.id | 2 |使用where 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ----------- + ---- -  + ------------- + 
集合中的5行（0.04秒）
  

---

如上所述，当父行数很少为100时，这开始崩溃，即使我们使用主键：

  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行（1分钟3.11秒）
  

（注意，我有意在缓慢的机器上测试，数据保存在缓慢的闪存盘上）

这里是解释，查找一个id第一个，在那）：

  mysql>解释select * from parent_top_3 where id = 1; 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ----------- + ---- -  + ------------- + 
 | id | select_type |表|类型| possible_keys |键| key_len | ref |行|额外| 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ----------- + ---- -  + ------------- + 
 | 1 | PRIMARY | < derived2> | ALL | NULL | NULL | NULL | NULL | 1000 |使用where 
 | 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 1000 | | 
 | 5 |相关子词|子| ref | PRIMARY，pid | pid | 5 | util.a.id | 179 |使用where 
 | 4 |相关子词|子| ref | PRIMARY，pid | pid | 5 | util.a.id | 179 |使用where 
 | 3 |相关子词|子| ref | pid | pid | 5 | util.a.id | 179 |使用where 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ----------- + ---- -  + ------------- + 
集合中的5行（56.01秒）
  

对于一行，即使在我的慢速机器上，56秒以上，也是两个数量级的不可接受的。

那么我们可以保存这个查询吗？它工作，它太慢了。

这是修改后的查询的解释计划。它看起来很糟糕或更糟：

  mysql>解释select * from parent_top_3a其中id = 1; 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ----------- + ---- -  + ------------- + 
 | id | select_type |表|类型| possible_keys |键| key_len | ref |行|额外| 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ----------- + ---- -  + ------------- + 
 | 1 | PRIMARY | < derived2> | ALL | NULL | NULL | NULL | NULL | 100 |使用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 |相关子词|子| ref | pid | pid | 5 | util.a.id | 179 |使用where 
 | 5 |相关子词|子| ref | PRIMARY，pid | pid | 5 | a.id | 179 |使用where 
 | 3 |相关子词|子| ref | PRIMARY，pid | pid | 5 | a.id | 179 |使用where 
 + ---- + -------------------- + ------------ + ------ + --------------- + ------ + --------- + ----------- + ---- -  + ------------- + 
集合中的7行（0.05秒）
  

但是它可以在1/20秒内快速完成三个数量级！

我们如何得到更快的parent_top_3a？我们创建三个视图，每个视图取决于前一个视图：

 将视图parent_top_1创建为
选择a。*，
（从child中选择max（id）parent_id = a.id）
 as maxid 
来自父a; 
 
将视图parent_top_2创建为
选择a。*，
（从child中选择max（id），其中parent_id = a.id和id  as maxidm1 
 from parent_top_1 a; 
 
将视图parent_top_3a创建为
选择a。*，
（从child中选择max（id），其中parent_id = a.id，id  as maxidm2 
 from parent_top_2 a; 
 

这不仅工作得更快，而且在MySQL以外的RDBMS上也是合法的。

让我们将父行数增加到12800，将子行数增加到1536（大多数博客帖子没有获得评论，对吧？））

  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行（1.01秒）
  

注意这些时间是MyIsam表;我会留给别人在Innodb上做计时。

---

但是使用Postgresql，在类似但不相同数据集，我们在上获得类似的时间，其中<​​/ code>涉及父的列的谓词：

postgres =＃select（从父级选择count（*））作为parent_count，（select count（*）
from child）
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）

时间：91.139 ms


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?

解决方案

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

这篇关于LIMIT返回非唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！