我有桌子(大约80’000行),看起来像
id,parentId,col1,col2,col3...
1,null,'A','B','C'
2,1,...
3,...
4,...
5,4,...
(一级父母 – 仅限儿童)
我需要获得所有依赖行 –
SELECT ...
FROM table
WHERE id = :id OR parentId = :id OR id IN (
SELECT parentId
FROM table
WHERE id = :id
)
但为什么这个请求工作缓慢而不是2请求 – 如果我先在PHP上获得parentId？
$t = executeQuery('SELECT parentId FROM table WHERE id = :Id;',$id);
if ($t) {
$id = $t;
}
$t = executeQuery('SELECT * FROM table WHERE id = :id OR parentId = :id ORDER BY id;',$id);
PS：max取决于行数< 70 PPS：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY product ALL PRIMARY,parentId NULL NULL NULL 73415 Using where
2 DEPENDENT SUBQUERY product const PRIMARY,parentId PRIMARY 4 const 1
最佳答案
将IN更改为等于=
SELECT ...
FROM table
WHERE id = :id OR parentId = :id OR id = (
SELECT parentId
FROM table
WHERE id = :id
)
或将其更改为联接：
SELECT ...
FROM table
inner join (
SELECT parentId
FROM table
WHERE id = :id
) s on s.parentID = table.id or s.parentID = table.parentID