Geek asks and answers

MYSQL is slow as a query with a subquery than 2 queries (+ php)

I have a table (about 80'000 rows) that looks like

id, parentId, col1, col2, col3...
 1,     null, 'A', 'B', 'C'
 2,        1, ...
 3,        1, ...
 4,     null, ...
 5,        4, ...

(only one parent-parent is a child)

and I need to get all the dependent rows -

SELECT ... 
FROM table 
WHERE id = :id OR parentId = :id OR id IN (
    SELECT parentId 
    FROM table 
    WHERE id = :id
    )

but why does this request work slowly instead of 2 requests - if I get parentId in php first?

$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 depends on lines <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
+5
source share
5 answers

Change INto equal=

SELECT ... 
FROM table 
WHERE id = :id OR parentId = :id OR id = (
    SELECT parentId 
    FROM table 
    WHERE id = :id
    )

or change it to a connection:

SELECT ... 
FROM table 
    inner join ( 
        SELECT parentId 
        FROM table 
        WHERE id = :id
    ) s on s.parentID = table.id or s.parentID = table.parentID
+2
source

, MySQL , , . , , id parent id, , .

+1

UNION

UNION INDEX, , .

SELECT *
FROM `table` 
WHERE id = :id OR parentId = :id
UNION
SELECT t1.*
FROM `table` t1 JOIN `table` t2 ON t2.parentId = t1.id AND t2.id = :id
+1

, "IN" WHERE . , MySQL , , IN, - , .

2 . . JOIN ( ).

0

An EXPLAINcan shed light on a problem for you.

See EXISTSor rewrite the query as JOIN.

0
source

More articles:


All Articles