mysql - 连接sql语句

Question

我有这样的声明:

SELECT board.*, numlikes 
FROM board
LEFT JOIN (SELECT 
              pins.board_id, COUNT(source_user_id) AS numlikes
           FROM likes 
           INNER JOIN pins ON pins.id = likes.pin_id 
           GROUP BY pins.board_id) likes ON board.id = likes.board_id
WHERE who_can_tag = '' 
ORDER BY numlikes DESC LIMIT 10

但我还需要将另外两个语句加入其中:

SELECT COUNT(owner_user_id) 
FROM repin 
INNER JOIN pins ON pins.id = repin.from_pin_id 
WHERE pins.board_id = '$id'

和

SELECT COUNT(is_following_board_id) 
FROM follow 
WHERE is_following_board_id = '$id'

我设法让第一个加入，但我在其他人中遇到了麻烦 - 认为它可能会太长。

有没有更快的执行方法？

Answer 1

理想情况下，从最小的结果集开始，然后开始连接到下一个最小的表。

您不希望数据库对一堆大表进行全表连接，然后在最后使用一个 where 子句删除数据库刚刚创建的 99% 的行。

在 Oracle 中，我做了:

SELECT *
  FROM big_table bt
  JOIN DUAL ON bt.best_filter_column='the_value'
--now there are only a few rows
  JOIN other_table_1 ...
  LEFT JOIN outer_join_tables ...

最后包含所有 OUTER JOINS，因为它们不会删除任何行，所以希望您已经过滤掉了很多行。