MYSQL fetch 10个帖子，每个w /表决计数，按表决计数排序，限于where子句的帖子 [英] MYSQL fetch 10 posts, each w/ vote count, sorted by vote count, limited by where clause on posts

问题描述

我想获取一组列出的投票数，按投票数排序（例如）

 帖子1 -  Post Body blah blah  - 投票：500 
 Post 2  -  Post Body blah blah  - 投票：400 
 Post 3  -  Post Body blah blah  - 票数：300 
 Post 4  -  Post Body blah blah - 投票：200 
  

我有两张表：

帖子 - 列 - id ，正文， is_hidden

投票 - 列 - id ， post_id ， vote_type_id

这是我试过的查询：

  SELECT p。*，v.yes_count 
 FROM posts p 
 LEFT JOIN 
（SELECT post_id，vote_type_id，COUNT （1）AS yes_count 
 FROM votes 
 WHERE（vote_type_id = 1）
 GROUP BY post_id 
 ORDER BY yes_count DESC 
 LIMIT 0，10）v 
 ON v.post_id = p.id 
 WHERE（p.is_hidden = 0）
 ORDER BY yes_count DESC 
 LIMIT 0，10 
  

正确性：上述查询几乎可以使用。子查看包括 votes for posts 有 is_hidden = 1 ，所以当我离开加入它 posts ，如果一个隐藏的帖子在前10（排名通过投票），我可以结束与NULL在 yes_count 字段。

性能：在我的dev机器上，上面的查询运行在.4sec。

索引：我在Votes表中有一个索引，涵盖以下字段： vote_type_id 和 post_id

strong>

  id select_type表类型possible_keys键key_len引用行额外
 1 PRIMARY p ALL NULL NULL NULL NULL 45985使用哪里;使用临时;使用filesort 
 1 PRIMARY< derived2> ALL NULL NULL NULL NULL 10 
 2 DERIVED votes ref VotingPost VotingPost 4 319881 Using where;使用索引;使用临时;使用filesort 
  

解决方案

尝试

  SELECT p。*，count（*）yes_count 
 FROM posts p 
 LEFT OUTER JOIN vote v ON（v.post_id = p。 id and v.vote_type_id = 1）
 WHERE p.is_hidden = 0 
 GROUP BY p.id 
 ORDER BY yes_count DESC 
 LIMIT 0,10 
  

由于这是mysql，只有p.id的组将工作（但这不会被移植到其他dbs） p>

I want to fetch a set of Posts w/ vote count listed, sorted by vote count (e.g.)

Post 1 - Post Body blah blah - Votes: 500
Post 2 - Post Body blah blah - Votes: 400
Post 3 - Post Body blah blah - Votes: 300
Post 4 - Post Body blah blah - Votes: 200

I have 2 tables:

Posts - columns - id, body, is_hidden
Votes - columns - id, post_id, vote_type_id

Here is the query I've tried:

SELECT p.*, v.yes_count
FROM posts p
LEFT JOIN
    (SELECT post_id, vote_type_id, COUNT(1) AS yes_count
    FROM votes 
    WHERE (vote_type_id = 1) 
    GROUP BY post_id
    ORDER BY yes_count DESC
    LIMIT 0, 10) v 
ON v.post_id = p.id
WHERE (p.is_hidden = 0)
ORDER BY yes_count DESC
LIMIT 0, 10  

Correctness: The above query almost works. The subselect is including votes for posts that have is_hidden = 1, so when I left join it to posts, if a hidden post is in the top 10 (ranked by votes), I can end up with records with NULL on the yes_count field.

Performance: I have ~50k posts and ~500k votes. On my dev machine, the above query is running in .4sec. I'd like to stay at or below this execution time.

Indexes: I have an index on the Votes table that covers the fields: vote_type_id and post_id

EXPLAIN

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  PRIMARY  p  ALL  NULL  NULL  NULL  NULL  45985  Using where; Using temporary; Using filesort
1  PRIMARY  <derived2>  ALL  NULL  NULL  NULL  NULL  10   
2  DERIVED  votes  ref  VotingPost  VotingPost  4     319881  Using where; Using index; Using temporary; Using filesort

解决方案

Try

SELECT p.*, count(*) yes_count
FROM posts p
LEFT OUTER JOIN votes v ON (v.post_id = p.id and v.vote_type_id = 1)
WHERE p.is_hidden = 0
GROUP BY p.id
ORDER BY yes_count DESC
LIMIT 0,10

Since this is mysql, group by only p.id s will work (but this won't be portable to other dbs)

这篇关于MYSQL fetch 10个帖子，每个w /表决计数，按表决计数排序，限于where子句的帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！