威锋网的慢查询（版本的）及优化 [复制链接]

SELECT tid,subject FROM pw_threads FORCE INDEX(idx_postdate) WHERE ifcheck=1 AND tid != '1' AND postdate >= '11111111111'  AND authorid= '1'  AND fid>12   ORDER BY hits DESC LIMIT 1,2

改为 SELECT tid,subject FROM pw_threads  WHERE ifcheck=1 AND tid != '1' AND postdate >= '11111111111'  AND authorid= '1'  AND fid>12   ORDER BY hits DESC LIMIT 1,2
这个走authorid索引

desc SELECT * FROM pw_ms_messages   WHERE  created_time >  '666666'  AND created_time <  '333333333333333333333'  ORDER BY modified_time DESC LIMIT 1,2;
加了索引：ALTER TABLE `pw_ms_messages` ADD INDEX `modifytime` ( `modified_time` )

还有很多SQL语句都是以modified_time排序的


SELECT * FROM pw_threads t WHERE t.fid= 'S'   AND t.lastpost>= 'S'  AND t.ifcheck='S' AND t.topped=N AND t.recycle=N ORDER BY t.postdate DESC  LIMIT N,N

改为
SELECT * FROM pw_threads t WHERE t.fid= 'S'     AND t.ifcheck='S' AND t.topped=N AND t.lastpost>= 'S' AND t.recycle=N ORDER BY t.postdate DESC  LIMIT N,N
程序修改：
function getSqlAdd($allowtop = false) {
        $sqladd = '';
        $this->_ifcheck && $sqladd .= " AND t.ifcheck='1'";
        $allowtop && $sqladd .= ' AND t.topped=0';
        $sqladd .= $this->sqladd;// 调换位子
        if($this->selectType!="recycle"){$sqladd .= ' AND t.recycle=0';} // by wangying
        return $sqladd;
    }


还有pw_online  建议把lastvisit设为索引

很好的经验分享，谢谢王MM，组合索引是最左前缀前缀原则，我测试了下不动fid在最前面，后面调换顺序也是可以走组合索引的idx_fid_ifcheck_topped_lastpost，是不是你这个还有什么特殊的需求