SQL如下:
select u.uid,u.cid,u.username,u.loginNum,u.flag from username as u where u.loginNum > 0 and u.flag ='0' and (u.cid = 796 or u.uid = '796') order by u.uid
已经在uid,cid,flag字段添加索引.
但explain显示还是全索引扫描.
----+-------------+-------+-------+---------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | u | index | PRIMARY,uid,cid,flag,user_index | PRIMARY | 4 | NULL | 3470 | Using where |
+----+-------------+-------+-------+---------------------------------+---------+---------+------+------+-------------+
select u.uid,u.cid,u.username,u.loginNum,u.flag from username as u where u.loginNum > 0 and u.flag ='0' and (u.cid = 796 or u.uid = '796') order by u.uid
已经在uid,cid,flag字段添加索引.
但explain显示还是全索引扫描.
----+-------------+-------+-------+---------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | u | index | PRIMARY,uid,cid,flag,user_index | PRIMARY | 4 | NULL | 3470 | Using where |
+----+-------------+-------+-------+---------------------------------+---------+---------+------+------+-------------+
alter table username add index(uid,cid)因为你里面是or的关系
union all
select u.uid,u.cid,u.username,u.loginNum,u.flag from username as u where u.loginNum > 0 and u.flag ='0' and u.uid = 796 order by u.uid
把cid上的索引改成 cid和uid的多列索引idx_cuid(cid,uid)
索引(cid,uid)
你可以show index from username 观察一下索引的分布。