各位老大，过来看看那个SQL语句性能高

[mrfinger]

这是第一个

SELECT 
id
FROM 
b,
(SELECT userid,pageid FROM user_access ua 
UNION
SELECT userid,pageid FROM user_page )
a
WHERE 
b.from_id = a.pageid AND
b.type = 1 AND 
b.from_id IN (12508,12465) AND
b.userid = a.userid AND
b.`status` = 0

这是第二个

SELECT 
id
FROM 
b
WHERE 
b.type = 1 AND 
b.from_id IN (12508,12465) AND
b.userid IN (
SELECT userid FROM user_access ua WHERE ua.pageid = b.from_id 
UNION
SELECT userid FROM user_page WHERE pageid = b.from_id 
) AND
b.`status` = 0

[dingyanwei]

据说用in不如用exsits

[ACMAIN_CHM]

show index from b
show index from user_access

explain SELECT ...

贴出来分析一下。

[mrfinger]

up啊

[paula2008]

从结果上看 是第二种吧，

[ACMAIN_CHM]

create index xxx1 on b (from_id,type,status,id);

SELECT id
FROM b
WHERE
b.type = 1 AND
b.from_id IN (12508,12465) AND
(exists (select 1 from FROM user_access WHERE pageid = b.from_id) or
exists (select 1 from FROM user_page WHERE pageid = b.from_id)) and
b.`status` = 0

[mrfinger]

第一个SQL语句
explain结果
1 PRIMARY <derived2> ALL \N \N \N \N 402 Using where
1 PRIMARY pw ALL \N \N \N \N 1357 Using where
2 DERIVED ua index \N PRIMARY 8 \N 153 Using index
3 UNION user_page index \N user_page_map_key 8 \N 249 Using index

第二个SQL语句
explain结果
1 PRIMARY pw ALL \N \N \N \N 1357 Using where
2 DEPENDENT SUBQUERY ua eq_ref PRIMARY PRIMARY 8 diitest.pw.from_id,func 1 Using index
3 DEPENDENT UNION user_page eq_ref user_page_map_key user_page_map_key 8 func,diitest.pw.from_id 1 Using index

show index from b
的结果
b 0 PRIMARY 1 id A 1357 \N \N BTREE
show index from user_access
的结果
user_access 0 PRIMARY 1 pageid A \N \N \N BTREE
user_access 0 PRIMARY 2 userid A 153 \N \N BTREE

[mrfinger]

ACMAIN_CHM：帮帮分析一下，谢谢

[minitoy]

b.type = 1 AND
b.from_id IN (12508,12465) AND
b.`status` = 0
把这些条件都放到括号里面去

[秋韩]

最好少用in 、or