SQL1:SELECT COUNT(*) FROM table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954'
SQL2:select * from table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36 SELECT COUNT(*) FROM table1 有32万条数据
SELECT COUNT(*) FROM table1 WHERE zplb0='954' 有10万条数据
SELECT COUNT(*) FROM table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' 有10万条数据索引:KEY `zplb0` (`zplb0`,`displayorder`,`lastposter`) explain SELECT COUNT(*) FROM cdb_threads WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954'
结果 Using where; Using indexexplain select * from cdb_threads WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36 结果 type = all ; extra = Using where; Using filesort
sql2执行要1秒多,非常慢。请问这样的sql如何优化好。谢谢!
SQL2:select * from table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36 SELECT COUNT(*) FROM table1 有32万条数据
SELECT COUNT(*) FROM table1 WHERE zplb0='954' 有10万条数据
SELECT COUNT(*) FROM table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' 有10万条数据索引:KEY `zplb0` (`zplb0`,`displayorder`,`lastposter`) explain SELECT COUNT(*) FROM cdb_threads WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954'
结果 Using where; Using indexexplain select * from cdb_threads WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36 结果 type = all ; extra = Using where; Using filesort
sql2执行要1秒多,非常慢。请问这样的sql如何优化好。谢谢!
SELECT COUNT(*) as k FROM table1 WHERE (displayorder>='0' ) and zplb0='954'
union all
SELECT COUNT(*) FROM table1 WHERE (displayorder='-2' ) and zplb0='954'
)
sql2要一秒多。