连接优化查询，按条件查询的时候，如何优化查询的时间

问题描述：

要维护两个表：

1.t_users表。里面有account stutus other字段

status有1，2，4，6，3，5，11各种状态

表有5W行以上

2.t_vps表。里面有vps，vacc，adsl，apwd字段

要求每一个vps的信息，以及对应个status的和

传统做法：
直接遍历t_vps，对每一个vps都查一下，让adsl和t_users的other字段相等，再count

优化做法做表的连接：

SELECT countid,a.other AS adsl_run,b.id as id,b.vps AS vps,b.vacc as vacc,b.vpwd as vpwd,b.adsl as adsl,b.apwd as apwd,b.description as description
                       FROM(SELECT count(id) AS countid,other FROM `t_users`
                       WHERE time > $tmpTimeStart AND time<$tmpTimeEnd AND $condition
                       GROUP BY other) AS a
                       RIGHT JOIN (SELECT id,vps,vacc,vpwd,adsl,apwd,description FROM `t_vps`) b ON a.other = b.adsl
                       GROUP BY adsl
                       ORDER BY id ";

 

转载于:https://www.cnblogs.com/caimuqing/p/5710267.html