怎么样提高 两表的多次关联查询性能？

[floy]

在数据库中，有两张表ms_property ，ms_messages

表ms_messages存放内容， 表ms_property存放属性，属性不是固定数量的，可能有很多，记录量约50W级
两表通过msgid关联，一条内容记录对应不定数的属性记录，记录数约6万级

表ms_property

propid msgid property
-----------------------------
1 1 11
2 1 22
3 1 33
4 1 44
5 1 AA
6 1 BB
7 1 CC
.
.
.
8 2 11
9 2 22
10 2 33
11 2 44
12 2 AA
13 2 BB
14 2 CC
.
.
.

表ms_messages

msgid message
----------------------------
1 msg1
2 msg2
.
.
.

为了查到符合指定属性要求的记录，我目前用下面的SQL，性能特别慢

SELECT msg.msgid,msg.content FROM ms_messages msg
JOIN ms_property prop ON prop.msgid = msg.msgid
WHERE msg.status = 1
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property = '11')
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property <> '22')
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property IN ( '11','AA'))
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property NOT IN ('33','BB'))
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property IN ('44,','22','CC')
GROUP BY msg.msgid
LIMIT 20

那位可以指定一下，怎么去优化SQL，或是有什么简洁的设计思路

[lds1ove]

Oracle ？？？

[lds1ove]

1
prop.msgid in(括弧里的多个语句可以合成一个 ....)

2 in 里边的 distinct 没必要

3 可以用 exists join 代替in

[lds1ove]

SELECT msg.msgid,msg.content FROM ms_messages msg
JOIN ms_property prop ON prop.msgid = msg.msgid
WHERE msg.status = 1
AND prop.msgid in(SELECT msgid FROM ms_property WHERE property <> '22'
and property IN ( '11','AA')
AND property NOT IN ('33','BB')
AND property IN ('44,','22','CC'))
GROUP BY msg.msgid
LIMIT 20

[thinclient]

索引有关列

[floy]

感谢回楼上，其实用的mysql

用过JOIN ms_property prop1 ON prop1.msgid = prop.msgid where prop1.property ***的方法，性能更差

[lds1ove]

SELECT msg.msgid,msg.content FROM ms_messages msg
JOIN ms_property prop ON prop.msgid = msg.msgid
WHERE msg.status = 1
AND prop.msgid in(
SELECT msgid FROM ms_property WHERE property IN ( '11','AA','44,','22','CC') 
AND property NOT IN ('33','BB','22'))
GROUP BY msg.msgid
LIMIT 20

[lds1ove]

[Quote=引用 5 楼 floy 的回复:]
感谢回楼上，其实用的mysql

用过JOIN ms_property prop1 ON prop1.msgid = prop.msgid where prop1.property ***的方法，性能更差
[/Quote]join 然后 prop1.msgid = prop.msgid两列建索引 不可能更差

[floy]

两个表都建有索引

表ms_property
propid（pk) msgid property都有是索引

[floy]

[Quote=引用 6 楼 lds1ove 的回复:]

SQL code
SELECT msg.msgid,msg.content FROM ms_messages msg
JOIN ms_property prop ON prop.msgid = msg.msgid
WHERE msg.status = 1
AND prop.msgid in(
SELECT msgid FROM ms_property WHERE property IN ( '……
[/Quote]

SELECT msgid FROM ms_property WHERE property IN ( '11','AA','44,','22','CC')
归并到一个条件里，查出来的数据就不对了

[claro]

MySQL的开发问题尽量不要题错版，以免发生不必要的错误和麻烦。

[floy]

[Quote=引用 10 楼 claro 的回复:]

MySQL的开发问题尽量不要题错版，以免发生不必要的错误和麻烦。
[/Quote]

呵呵，主要还是这个版的高手多，人气旺，不过这问题不局限于mysql来解决，主要涉及如何设计表结构和构建SQL语句