On Mon, Jun 22, 2015 at 01:33:20PM +0000, Rhys Campbell wrote:
Ah. That makes a lot of sense, but...
Ot.col2 doesn't refer to the PK of the underlying table but it is at the head of a unique index (multiple columns)
So, the outer query is: t1 p LEFT OUTER JOIN t2 ot ON p.col1 = ot.col2 And the t2 is a VIEW defined as: SELECT /* COLS REMOVED */ FROM `t2` `p1` WHERE (`p1`.`col1` = (SELECT ...)) Then, t2.col2= ... -- ON expression t2.col1= ... -- WHERE in the VIEW definition. Is the unique index defined over (col2, col1) ? Table Elimination kicks in only when the optimizer figures out that table t2 can have at most one matching record for each record of t1. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog