mysql join 3 table retain order of data order by used in subquery
This kludge allegedly works:  Tack LIMIT 999999999 onto the subquery.  That forces the optimizer to use the ORDER BY.  Normally (in newer versions of MySQL), the Optimizer simply throws away the ORDER BY in certain subqueries -- because the SQL Standard says that a subquery delivers an unordered set.  A reference.

(Not relevant in your case.)  Meanwhile, explicitly add ORDER BY in the subquery; don't depend on GROUP BY providing an ordering.  That MySQL 'extension' is in flux.  (If the GROUP BY and ORDER BY are the same, the Optimizer is smart enough to do them together.)

Because of the OR, the only practical way to do the subquery is with a full table scan of the 2M rows.

Optimization tip:  Smaller --> faster, especially if the table is too big to be cached.  So...


Don't use BIGINT (8 bytes) when something smaller would suffice.
Don't use INT (4 bytes) for a 'flag'.  TINYINT is 1 byte.
Don't use TEXT when VARCHAR(255 or less) would suffice.  (There is an extra savings when a tmp table is needed.)