[sql]SELECT mf.uid
FROM cdb_student st, cdb_memberfields mf
WHERE st.used = '1'
AND st.xh = mf.field_1
AND st.xm = mf.field_2
AND st.sfz = mf.field_3[/sql]
发现论坛打开非常缓慢 于是打开mysqladmin,哇塞，那个表锁严重到则
[bash]
mysqladmin processlist
+---------+------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 2938693 | root | localhost | discuz | Query | 178 | Copying to tmp table | SELECT mf.uid, m.username
FROM cdb_student st, cdb_memberfields mf, cdb_members m
WHERE st.used = |
| 2938757 | root | localhost | discuz | Query | 153 | Locked | UPDATE cdb_members SET prompt=prompt|1 WHERE uid='12750' |
| 2938758 | root | localhost | discuz | Query | 153 | Locked | SELECT s.sid, s.styleid, s.groupid='6' AS ipbanned, s.pageviews AS spageviews, s.lastolupdate, s.sec |
[/bash]
[bash]mysqladmin kill 2938693[/bash]
看来要改进表结构了，考虑下使用索引
顺便检验一下一下各种查询耗费时间比较
[sql]
SELECT st.xh, mf.field_7
FROM cdb_student st, cdb_memberfields mf
WHERE st.used = '1'
AND st.xh = mf.field_1
AND st.xm = mf.field_2
AND st.sfz = mf.field_3
LIMIT 0 , 30
[/sql]
查询花费 2.6541 秒
[sql]
SELECT st.used, st.xh, mf.field_1, st.ID, st.times
FROM cdb_student st, cdb_memberfields mf
WHERE st.used = '1'
AND st.xh = mf.field_1
AND st.xm = mf.field_2
AND st.sfz = mf.field_3
LIMIT 0 , 30
[/sql]
查询花费 2.6538 秒
可以看出确实非常耗费资源
好在MySQl有查询缓存，第二次查询非常快，不过，目前还是先左功能把，性能往后考虑