站内消息表索引设计,顶者有分,分不够重开帖子.

Question

会员消息表: message
字段: pkid, uid1(发件人id), uid2(收件人id), messageid(消息id, 0: 普通消息; >0: 非普通消息.)pkid uid1 uid2 messageid
1 7 9 0
2 7 9 5
3 7 9 0
4 7 8 0
5 8 7 6
6 9 7 3
7 7 9 3
8 8 9 0
9 17 23 3
.....网站需要的功能:
1. 读取单条记录.指定pkid.
pkid uid1 uid2 messageid
1 7 9 02. 读取指定id(如:7)的所有发送的邮件.
pkid uid1 uid2 messageid
1 7 9 0
2 7 9 5
3 7 9 0
4 7 8 0 
7 7 9 3
.....
3. 读取指定id(如:7)的所有收到的邮件.
pkid uid1 uid2 messageid
5 8 7 6
6 9 7 3
.....
4. 读取指定的id(如:7)和指定的id(如:8)之间的通信记录
pkid uid1 uid2 messageid 
4 7 8 0
5 8 7 6 
.....5. 读取指定的id(如:7)和所有联系的记录.(接收和发送分开计算)(messageid=0和messgeid>0的记录要分开计算)
uid1 uid2 messageid(=0的记录) messageid(>0的记录)
7 9 2 2 
7 8 1 0
8 7 0 1
9 7 0 1 
8 9 1 0
17 23 0 1
.....1. 希望给出message表的索引设计. 
2. 希望给出上面第5条的sql语句..谢谢.
分不够重开帖子.
知道csdn排版从来就不让人省心. 贴个图片上来.

Answer 1

pkid主键
uid1 普通索引
uid2 普通索引

Answer 2

第五条给错了.
应该是5. 读取指定的id(如:7)和所有联系的记录.(接收和发送分开计算)(messageid=0和messgeid>0的记录要分开计算)
uid1    uid2    messageid(=0的记录)    messageid(>0的记录)    
7    9    2            2 
7    8    1            0
8    7    0            1
9    7    0            1  
.....

Answer 3

两个普通索引用在"4. 读取指定的id(如:7)和指定的id(如:8)之间的通信记录"上面的时候会不会有作用?

Answer 4

比如读取指定的id(如:7)和指定的id(如:8)之间的通信记录
我会写
select * from [message] ([uid1]=7 and [uid2]=8) or ([uid1]=8 and [uid2]=7)
这种情况下会用哪一个索引?

Answer 5

declare @uid1 int
declare @uid2 int
declare @uid3 int
declare @uid4 int
set @uid1 = 7
set @uid2 = 8
set @uid3 = 8
set @uid4 = 7--1
select pkid,uid1,uid2,messageid
from tb
where pkid = @pkid--2
select pkid,uid1,uid2,messageid
from tb
where uid1 = @uid1--3
select pkid,uid1,uid2,messageid
from tb
where uid2 = @uid2--4
select pkid,uid1,uid2,messageid
from tb 
where uid1 = @uid1 and uid2 = @uid2     -- 7 发 8  
union all
select pkid,uid1,uid2,messageid
from tb 
where uid1 = @uid3 and uid2 = @uid4     -- 7 收 8--5
select uid1,uid2,sum(case when messageid = 0 then 1 else 0 end)message0,
                 sum(case when messageid > 0 then 1 else 0 end)message1
from tb
where uid1 = @uid1    
group by uid1,uid2
union all
select uid1,uid2,sum(case when messageid = 0 then 1 else 0 end)message0,
                 sum(case when messageid > 0 then 1 else 0 end)message1
from tb
where uid2 = @uid1
group by uid1,uid2

Answer 6

索引建立如1楼给的，查询语句避免用 * or in 等！

Answer 7

现在我建立的索引是
主键: uid1, uid2, pkid
索引1: uid2, uid1, pkid
索引2: pkid直接改成1楼的方法吗?多谢你的sql语句, 

Answer 8

对，主键pkid可以满足了，其他两个字段作索引。

Answer 9

select * from [message] ([uid1]=7 and [uid2]=8) or ([uid1]=8 and [uid2]=7)
为什么要用到OR  那样索引无效的