改进我的查询以在主页上获取帖子 - 需要 0.5171 秒

Question

该查询运行速度非常慢；它曾经是 3 个单独的查询，但我已将其合并为 1 个。我认为它很慢，因为它正在执行子选择，但我认为可能有更好的方法来查询此信息。

* 背景：*

users 只是用户帐户。
posts 是所有帖子内容。
posts_int 只有 2 个字段，postid 和 intid。这存储帖子的兴趣 ID。
int_usr 也只有 2 个字段，userid 和 intid。它存储用户的兴趣 ID。

所有“intid”字段名称都基于另一个仅包含每个兴趣的 id 和名称的表。此查询返回该用户与其所选兴趣相关的所有帖子，以整数形式存储在 int_usr 中。

查询：

SELECT DISTINCT 
users.first_name,
users.last_name,
posts.id,
posts.userid,
posts.comments,
posts.date,
posts.comment 
FROM posts 
LEFT JOIN users ON 
posts.userid = users.id 
LEFT JOIN posts_int ON 
posts.id = posts_int.postid 
WHERE 
posts_int.intid IN (
SELECT DISTINCT 
  intid 
FROM int_usr 
WHERE 
  intid IN (
      SELECT DISTINCT 
      intid 
       FROM int_usr 
       WHERE 
      userid = '1' 
  ) 
ORDER BY intid ASC 
)
AND posts.deleted = '0' 
AND users.deleted = '0' 
ORDER BY 
posts.id DESC 
LIMIT 0,12

感谢任何帮助！

Ryan

编辑： 这是您要求的解释：

* EXPLAIN SQL: *

+----+--------------------+-----------+--------+----------------+---------+---------+---------------------------+------+----------------------------------------------+
| id | select_type        | table     | type   | possible_keys  | key     | key_len | ref                       | rows | Extra                                        |
+----+--------------------+-----------+--------+----------------+---------+---------+---------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | posts_int | ALL    | postid         | NULL    | NULL    | NULL                      |   87 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | posts     | eq_ref | PRIMARY,userid | PRIMARY | 4       | bunchify.posts_int.postid |    1 | Using where                                  |
|  1 | PRIMARY            | users     | eq_ref | PRIMARY        | PRIMARY | 4       | bunchify.posts.userid     |    1 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | int_usr   | ALL    | NULL           | NULL    | NULL    | NULL                      |  288 | Using where; Using temporary                 |
|  3 | DEPENDENT SUBQUERY | int_usr   | ref    | userid         | userid  | 4       | const                     |   19 | Using where; Using temporary                 |
+----+--------------------+-----------+--------+----------------+---------+---------+---------------------------+------+----------------------------------------------+
5 rows in set (0.00 sec)

Answer 1

您可以通过消除额外的嵌套级别

posts_int.intid IN (
SELECT DISTINCT 
  intid 
FROM int_usr 
WHERE 
  intid IN (
      SELECT DISTINCT 
      intid 
       FROM int_usr 
       WHERE 
      userid = '1' 
  ) 
ORDER BY intid ASC 
)

然后将左联接更改为内联接来简化一些事情，因为无论如何您都会检查 where 子句中的正确表。

---

Something like

SELECT DISTINCT <blah>
  FROM posts 
  JOIN users 
       ON posts.userid = users.id 
  JOIN posts_int 
       ON posts.id = posts_int.postid 
  JOIN int_usr 
       on posts_int.intid = int_usr.intid and int_usr.userid = '1'
 where posts.deleted = '0' 
   AND users.deleted = '0' 
ORDER BY posts.id DESC 
LIMIT 0,12

Answer 2

这应该工作得更快

SELECT DISTINCT  users.first_name, users.last_name, users.fb_userid, users.fb_username, posts.id, posts.userid, posts.likes, posts.dislikes, posts.comments, posts.date, posts.youtube_id, posts.media_image, posts.link_title, posts.link_description, posts.link_url, posts.comment  
FROM posts  
LEFT JOIN users ON  posts.userid = users.id  
LEFT JOIN posts_int ON  posts.id = posts_int.postid  
WHERE  posts_int.intid IN (SELECT DISTINCT intid  
                           FROM int_usr  
                           WHERE userid = '1' 
                           ORDER BY intid ASC) AND posts.deleted = '0'  AND users.deleted = '0'  
ORDER BY  posts.id DESC  LIMIT 0,12 

如果这能达到我希望的效果，那么它将是最快的

SELECT DISTINCT  users.first_name, users.last_name, users.fb_userid, users.fb_username, posts.id, posts.userid, posts.likes, posts.dislikes, posts.comments, posts.date, posts.youtube_id, posts.media_image, posts.link_title, posts.link_description, posts.link_url, posts.comment  
FROM posts  
LEFT JOIN users ON  posts.userid = users.id  
LEFT JOIN posts_int ON  posts.id = posts_int.postid 
left join int_user ON  int_user.userid = posts.userid and int_user.intid = posts_int.intid and int_user.userid = '1' 
WHERE  posts_int.intid IN AND posts.deleted = '0'  AND users.deleted = '0'  
ORDER BY  posts.id DESC  LIMIT 0,12