这么简单的sql语句怎么会这么慢？？

[veardn]

我的sql语句是要把该用户最近"被回复"过的帖子和最近"回复"过的帖子都找出来

其中umc_card_auction是发帖表，umc_card_bid是回帖表，

umc_card_auction主键是card_auction_id,umc_card_bid的主键是card_bid_id,外键是card_auction_id,都加了索引，其他字段都没加

语句如下

SELECT DISTINCT a.card_auction_id,a.member_id, a.card_auction_name,a.df,a.last_reply
FROM umc_card_auction a LEFT JOIN umc_card_bid c
ON a.card_auction_id = c.card_auction_id
WHERE ( a.df = 1 OR a.df = 2 )
AND (a.member_id = 1068 OR c.member_id = 1068)
order by a.last_reply desc

有人能帮忙分析下吗？？？

[veardn]

忘记说了，两张表的记录都是6000多

[claro]

原语句执行多长时间，执行以下语句多长时间？

SELECT DISTINCT a.card_auction_id,a.member_id, a.card_auction_name,a.df,a.last_reply 
FROM umc_card_auction a LEFT JOIN umc_card_bid c 
ON a.card_auction_id = c.card_auction_id 
WHERE ( a.df = 1 OR a.df = 2 ) 
AND (a.member_id = 1068 OR c.member_id = 1068)  

[veardn]

总共耗时24秒左右

[claro]

6000算小表了。

[veardn]

加不加order by 都很慢

[veardn]

是啊，我也不明白，其他语句都很快，就这条慢

[cjzm83]

SELECT 
	a.card_auction_id,
	a.member_id, 
	a.card_auction_name,
	a.df,
	a.last_reply  
FROM umc_card_auction a
WHERE ( a.df = 1 OR a.df = 2 )  
     AND a.member_id = 1068
order by a.last_reply desc

[kevn]

[Quote=引用楼主 veardn 的回复:]
我的sql语句是要把该用户最近"被回复"过的帖子和最近"回复"过的帖子都找出来

其中umc_card_auction是发帖表，umc_card_bid是回帖表，

umc_card_auction主键是card_auction_id,umc_card_bid的主键是card_bid_id,外键是card_auction_id,都加了索引，其他字段都没加

语句如下

SELECT……
[/Quote]
为什么on 后面又用了where，不是继续and，用where前面还用left join 干什么

[veardn]

[Quote=引用 7 楼 cjzm83 的回复:]

SQL code

SELECT
a.card_auction_id,
a.member_id,
a.card_auction_name,
a.df,
a.last_reply
FROM umc_card_auction a
WHERE ( a.df = 1 OR a.df = 2 )
AND a.member_id = 1068
……
[/Quote]

不明白，我用的是mysql，但这个地方的语法应该是一样的吧

如果不用left join ，那该用户所发的没人回复的帖子就查不出来了。

[claro]

CRTL+L看一下执行计划，是否建议添加索引。

[kevn]

[Quote=引用 9 楼 veardn 的回复:]
引用 7 楼 cjzm83 的回复:

SQL code

SELECT
a.card_auction_id,
a.member_id,
a.card_auction_name,
a.df,
a.last_reply
FROM umc_card_auction a
WHERE ( a.df = 1 OR a.df = 2 )
AND a.member_id = 1068
……
[/Quote]
我的意思是用了where后， left join 达不到效果了，另外既然主外键都在语句中都已关联号
是否还有必要a.member_id = 1068 OR c.member_id = 1068 两个都要=1608？

[veardn]

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

CRTL+L看一下执行计划，是否建议添加索引。
[/Quote]

我用的mysql,好像没有执行计划

a.member_id = 1068 OR c.member_id = 1068是要把1068所发的帖子和回复的帖子都查出来

[fengyun142415]

虽然语句比较简单，但是条件复杂

[abuying]

试一下去年distinct,这里慢 了
其实C表没这么用到，改一下吧

SELECT  a.card_auction_id,a.member_id, a.card_auction_name,a.df,a.last_reply  
FROM umc_card_auction a 
WHERE a.df in(1,2)  and a.member_id = 1068
union all
SELECT  a.card_auction_id,a.member_id, a.card_auction_name,a.df,a.last_reply  
FROM umc_card_auction a 
WHERE a.df in(1,2)  and  
  exists(select 1 from umc_card_bid c where a.card_auction_id = c.card_auction_id and c.member_id = 1068)
order by a.last_reply desc

[veardn]

14楼的执行速度很快，但结果稍微有点不对，我自己再看看吧

[abuying]

[Quote=引用 15 楼 veardn 的回复:]

14楼的执行速度很快，但结果稍微有点不对，我自己再看看吧
[/Quote]
你可以把distinct 放在外层SQL语句中
不对，可能是有重复吧！加上distinct

select distinct * from
(
SELECT  a.card_auction_id,a.member_id, a.card_auction_name,a.df,a.last_reply  
FROM umc_card_auction a 
WHERE a.df in(1,2)  and a.member_id = 1068
union all
SELECT  a.card_auction_id,a.member_id, a.card_auction_name,a.df,a.last_reply  
FROM umc_card_auction a 
WHERE a.df in(1,2)  and  
  exists(select 1 from umc_card_bid c where a.card_auction_id = c.card_auction_id and c.member_id = 1068)
) t
order by last_reply desc