maria-discuss team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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