maria-discuss team mailing list archive
Mailing list archive
Re: View missing from EXPLAIN after ORDER BY removed from definition
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:
LEFT OUTER JOIN t2 ot
ON p.col1 = ot.col2
And the t2 is a VIEW defined as:
/* COLS REMOVED */
FROM `t2` `p1`
WHERE (`p1`.`col1` = (SELECT ...))
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.
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog