这是第一个
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
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
show index from user_access explain SELECT ...贴出来分析一下。
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 indexshow 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
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
b.from_id IN (12508,12465) AND
b.`status` = 0
把这些条件都放到括号里面去