mysql - 将mysql子查询转换为join

Question

我是 mysql 数据库的新手，我正在尝试创建一个使用连接的查询。我编写的当前查询需要 1 秒的时间来执行，我已经进行了研究并发现连接是减少执行时间的最佳解决方案，

我使用的查询是:

SELECT count(1) as total 
FROM `mytable` 
where pid IN (
     SELECT pid FROM `maps` 
     where `node_id` = (
          SELECT nid FROM shop 
          WHERE value = 'selling' 
            and parent = '170' 
            and  tid = 1
          )
     and  tid = 1
) 
and `pnumber` IN (
     select pnumber 
     FROM users_pro 
     where value = 'hulo' 
     and  tid = 1
)
and tid = 1

拜托，我们将不胜感激。

谢谢..

Answer 1

SELECT count(distinct mytable.*) as total 
FROM mytable 
JOIN maps
  ON mytable.pid = maps.pid
  AND maps.tid = mytable.tid
JOIN shop
  ON maps.node_id = shop.nid
  AND shop.value = 'selling' 
  AND shop.parent = '170' 
  AND shop.tid = mytable.tid
JOIN users_pro 
  ON mytable.pnumber = users_pro.pnumber
  AND users_pro.value = 'hulo' 
  AND users_pro.tid = mytable.tid
WHERE mytable.tid=1