MySQLa, Version: 5.5.40 (MySQL Community Server (GPL)). started with:
TCP Port: 6306, Named Pipe: /tmp/mysql.sock
Time Id Command Argument
# Time: 151105 15:14:35
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.312602 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 185087
use ultrax;
SET timestamp=1446707675;
SELECT count(*) FROM pre_forum_thread where authorid='3' and displayorder!=-2 and displayorder!=-1;
# Time: 151105 15:14:37
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.750111 Lock_time: 0.000000 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446707677;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 15:14:40
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.687609 Lock_time: 0.000000 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446707680;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.296976 Lock_time: 0.000000 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446707680;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 15:14:41
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.890738 Lock_time: 0.000000 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446707681;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 15:14:42
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.609502 Lock_time: 0.000000 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446707682;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:03:29
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 55.031954 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 743696
SET timestamp=1446710609;
select a.tid,a.subject,a.dateline,a.views,a.replies,a.author,a.authorid,b.message from pre_forum_thread a join pre_forum_post b on a.tid=b.tid where a.attachment!=0 and a.displayorder>=0 and b.first=1 order by tid desc limit 0,10;
# Time: 151105 16:04:18
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 46.578721 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 743698
SET timestamp=1446710658;
select a.tid,a.subject,a.dateline,a.views,a.replies,a.author,a.authorid,b.message from pre_forum_thread a join pre_forum_post b on a.tid=b.tid where a.attachment!=0 and a.displayorder>=0 and b.first=1 order by tid desc limit 0,10;
# Time: 151105 16:04:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.765712 Lock_time: 1.531270 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446710670;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:04:46
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 7.078215 Lock_time: 0.000000 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446710686;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:13:25
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.718823 Lock_time: 0.406255 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446711205;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:13:52
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.546959 Lock_time: 1.296892 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446711232;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:13:53
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.609459 Lock_time: 0.625008 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446711233;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:14:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.687573 Lock_time: 0.656259 Rows_sent: 12 Rows_examined: 185111
SET timestamp=1446711270;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3 and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
作者: nycle 时间: 2015-11-6 16:08
几个原则：
1.索引通常建在过滤字段或关联字段。
2.尽量少count，数据量大时，考虑变通实现。
3.索引字段上的不等于的条件，采用变通的实现方式。作者: kider 时间: 2015-11-9 16:42
另外你需重点关注下，这样的语句为啥出奇的慢：
select a.tid,a.subject,a.dateline,a.views,a.replies,a.author,a.authorid,b.message from pre_forum_thread a join pre_forum_post b on a.tid=b.tid where a.attachment!=0 and a.displayorder>=0 and b.first=1 order by tid desc limit 0,10;
检查表字段a.tid=b.tid，a.displayorder，b.first=1是否有索引。
order by 后的tid字段前最好加上表名，因为两个表中都有这个字段。作者: kider 时间: 2015-11-9 16:47
至少这句话，在我这里的discuz数据库中运行很快。