MySQL dependent subqueries when should be independent

when I explain this query:

SELECT DISTINCT(u.mail)
FROM ep_point_transactions pt
  JOIN ep_transaction_status ts ON ts.tid = pt.tid
  JOIN users u ON u.uid = pt.uid
WHERE ts.anulada IS NULL
  AND pt.anulada IS NULL
  AND ts.source = 'rake'
  AND (pt.educapuntos * pt.multiplicador) >= 7500
  AND ts.timestamp < '2010-11-30 23:00:00'
  AND pt.uid NOT IN (
    SELECT distinct(pt2.uid)
    FROM ep_point_transactions pt2
      JOIN ep_transaction_status ts2 ON ts2.tid = pt2.tid
    WHERE ts2.anulada IS NULL
      AND pt2.anulada IS NULL
      AND ts2.source = 'rake'
      AND ts2.timestamp > '2010-11-30 23:00:00'
  );

I'm obtaining this output:

+----+--------------------+-------+--------+----------------+---------+---------+-------------------------+------+------------------------------+
| id | select_type        | table | type   | possible_keys  | key     | key_len | ref                     | rows | Extra                        |
+----+--------------------+-------+--------+----------------+---------+---------+-------------------------+------+-------开发者_如何学运维-----------------------+
|  1 | PRIMARY            | ts    | ref    | PRIMARY,source | source  | 194     | const                   | 2997 | Using where; Using temporary |
|  1 | PRIMARY            | pt    | ref    | PRIMARY        | PRIMARY | 4       | educapoker.ts.tid       |   30 | Using where                  |
|  1 | PRIMARY            | u     | eq_ref | PRIMARY        | PRIMARY | 4       | educapoker.pt.uid       |    1 |                              |
|  2 | DEPENDENT SUBQUERY | ts2   | ref    | PRIMARY,source | source  | 194     | const                   | 2997 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | pt2   | eq_ref | PRIMARY        | PRIMARY | 8       | educapoker.ts2.tid,func |    1 | Using where                  |
+----+--------------------+-------+--------+----------------+---------+---------+-------------------------+------+------------------------------+

My doubt here is why MySQL is considering the subquery dependent if I can execute it isolately.

As far as I know, in a dependent subquery, MySQL executes the subquery once for each parent query row, so it can be really inefficient.

Can anyone bring me some light here, please? Thanks.

I think it's the NOT IN(SELECT ...). Try rewriting it to LEFT JOIN with IS NULL

SELECT 
  tid
FROM 
  ep_transaction_status ts
LEFT JOIN 
  ep_transaction_status ts2
USING (tid)
WHERE 
  ts.anulada IS NULL 
  AND ts.source = 'rake'
  AND ts2.anulada IS NULL
  AND ts2.timestamp > '2010-11-30 23:00:00'
  AND ts2.tid IS NULL

Which BTW seems to me to be same as

SELECT 
  tid
FROM 
  ep_transaction_status ts
WHERE 
  ts.anulada IS NULL 
  AND ts.source = 'rake'
  AND ts.timestamp <= '2010-11-30 23:00:00'