Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL thinks subquery is derived when it is not!

EXPLAIN SELECT node_id 
          FROM node 
         WHERE person_id IN (SELECT person_id 
                               FROM user 
                              WHERE is_locked = 0);

Results in MySql telling me the subquery is derived. But it's not!

(I know this could easily be re-written as a JOIN, but I want to know why MySQL thinks this is a dependent subquery.)

like image 299
Sean Avatar asked Oct 25 '22 01:10

Sean


1 Answers

This is a bug in the MySQL Query Optimizer. It would seem that, if the table in the subquery matches the table in the main query, it is considered a dependent subquery even if it obviously should not be, and there's no easy fix. Sorry; go for the join.

like image 66
Matchu Avatar answered Nov 08 '22 10:11

Matchu