有一个查询语名用不了索引，要用3~4秒钟

SELECT a.aid AS a_aid, a.tid AS a_tid, a.pid AS a_pid, a.dateline AS a_dateline, a.readperm AS a_readperm, a.filename AS a_filename, a.description AS a_description, a.filetype AS a_filetype, a.filesize AS a_filesize, a.attachment AS a_attachment, a.downloads AS a_downloads, t.* FROM cdb_attachments a LEFT JOIN cdb_threads t ON t.tid = a.tid WHERE a.filetype IN ('image/bmp', 'image/gif', 'image/jpeg', 'image/pjpeg', 'image/png') AND t.displayorder >= 0 ORDER BY dateline DESC LIMIT 0,10;

希望改进一下
文件也不是很多，才十多万。但查询速度很慢
根本用不了索引。

[ 本帖最后由 swder 于 2006-9-28 17:40 编辑 ]

原帖由 swder 于 2006-9-12 09:33 发表
希望改进一下
文件也不是很多，才十多万。但查询速度很慢
根本用不了索引。

文件数不是关键，而是没用到索引，整表都要扫描完，当然慢。。。。。。。。。

顶呀，这句话想改也改不了呀，是SUPESITE 加密的代码，晕哟，。。。。。。
麻烦大大改下。
本来里面就有一个isimage的字段的。。。。。。。。。。。。。。。。

discuz 5里面才有 isimage字段的。
discuz 4里面没有isimage字段，所以只能这样读取了。速度会受到影响，不过可以放心的是，由于有缓存机制了，数据被读取后就被缓存起来了，下次直接从缓存里面读取，不影响速度了

几分钟一次 还是有些影响了 希望能判断版本改一下

1. 
   
2. # User@Host: discuz[discuz] @ localhost [127.0.0.1]
   
3. # Query_time: 4  Lock_time: 0  Rows_sent: 10  Rows_examined: 340346
   
4. SELECT a.aid AS a_aid, a.tid AS a_tid, a.pid AS a_pid, a.dateline AS a_dateline, a.readperm AS a_readperm, a.filename AS a_filename, a.description AS a_description, a.filetype AS a_filetype, a.filesize AS a_filesize, a.attachment AS a_attachment, a.downloads AS a_downloads, t.* FROM cdb_attachments a LEFT JOIN cdb_threads t ON t.tid = a.tid WHERE a.filetype IN ('image/bmp', 'image/gif', 'image/jpeg', 'image/pjpeg', 'image/png') AND t.supe_pushstatus > 0 AND t.displayorder >= 0 ORDER BY dateline DESC LIMIT 0,10;
   
5. # Time: 060929  9:58:01
   
6. # User@Host: discuz[discuz] @ localhost [127.0.0.1]
   
7. # Query_time: 4  Lock_time: 0  Rows_sent: 5  Rows_examined: 113482
   
8. SELECT a.aid AS a_aid, a.tid AS a_tid, a.pid AS a_pid, a.dateline AS a_dateline, a.readperm AS a_readperm, a.filename AS a_filename, a.description AS a_description, a.filetype AS a_filetype, a.filesize AS a_filesize, a.attachment AS a_attachment, a.downloads AS a_downloads, t.* FROM cdb_attachments a LEFT JOIN cdb_threads t ON t.tid = a.tid WHERE a.filetype IN ('image/bmp', 'image/gif', 'image/jpeg', 'image/pjpeg', 'image/png') AND t.supe_pushstatus > 0 AND t.displayorder >= 0 ORDER BY a.dateline DESC LIMIT 0,5;
   
9. # Time: 060929 10:07:19
   
10. # User@Host: discuz[discuz] @ localhost [127.0.0.1]
    
11. # Query_time: 4  Lock_time: 0  Rows_sent: 5  Rows_examined: 113476
    
12. SELECT a.aid AS a_aid, a.tid AS a_tid, a.pid AS a_pid, a.dateline AS a_dateline, a.readperm AS a_readperm, a.filename AS a_filename, a.description AS a_description, a.filetype AS a_filetype, a.filesize AS a_filesize, a.attachment AS a_attachment, a.downloads AS a_downloads, t.* FROM cdb_attachments a LEFT JOIN cdb_threads t ON t.tid = a.tid WHERE a.filetype IN ('image/bmp', 'image/gif', 'image/jpeg', 'image/pjpeg', 'image/png') AND t.supe_pushstatus > 0 AND t.displayorder >= 0 ORDER BY a.dateline DESC LIMIT 0,5;
    
13. # Time: 060929 10:07:32
    
14. # User@Host: discuz[discuz] @ localhost [127.0.0.1]
    
15. # Query_time: 3  Lock_time: 0  Rows_sent: 10  Rows_examined: 340328
    
16. SELECT a.aid AS a_aid, a.tid AS a_tid, a.pid AS a_pid, a.dateline AS a_dateline, a.readperm AS a_readperm, a.filename AS a_filename, a.description AS a_description, a.filetype AS a_filetype, a.filesize AS a_filesize, a.attachment AS a_attachment, a.downloads AS a_downloads, t.* FROM cdb_attachments a LEFT JOIN cdb_threads t ON t.tid = a.tid WHERE a.filetype IN ('image/bmp', 'image/gif', 'image/jpeg', 'image/pjpeg', 'image/png') AND t.supe_pushstatus > 0 AND t.displayorder >= 0 ORDER BY dateline DESC LIMIT 0,10;
    
17. # Time: 060929 10:13:12
    
18. # User@Host: discuz[discuz] @ localhost [127.0.0.1]
    
19. # Query_time: 3  Lock_time: 0  Rows_sent: 5  Rows_examined: 113483
    
20. SELECT a.aid AS a_aid, a.tid AS a_tid, a.pid AS a_pid, a.dateline AS a_dateline, a.readperm AS a_readperm, a.filename AS a_filename, a.description AS a_description, a.filetype AS a_filetype, a.filesize AS a_filesize, a.attachment AS a_attachment, a.downloads AS a_downloads, t.* FROM cdb_attachments a LEFT JOIN cdb_threads t ON t.tid = a.tid WHERE a.filetype IN ('image/bmp', 'image/gif', 'image/jpeg', 'image/pjpeg', 'image/png') AND t.supe_pushstatus > 0 AND t.displayorder >= 0 ORDER BY a.dateline DESC LIMIT 0,5;
    

复制代码

是不是你设置的缓存更新间隔太小了点吧.
如果你的论坛是d5,那么你修改config.php,将里面的$bbsver的值修改为5,就可以用isimage字段判断了