Hi Sergey,

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)

Value of optimizer_switch…

index_merge=ON,index_merge_union=ON,index_merge_sort_union=ON,index_merge_intersection=ON,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=ON,derived_merge=ON,derived_with_keys=ON,firstmatch=ON,loosescan=ON,materialization=ON,in_to_exists=ON,semijoin=ON,partial_match_rowid_merge=ON,partial_match_table_scan=ON,subquery_cache=ON,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=ON,semijoin_with_cache=ON,join_cache_incremental=ON,join_cache_hashed=ON,join_cache_bka=ON,optimize_join_buffer_size=off,table_elimination=ON,extended_keys=ON,exists_to_in=ON

When I set the table_elimination flag to OFF we get back to the original plan...

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	P1	index	\N	--	4	\N	85977207	100	Using index; Using temporary; Using filesort
1	PRIMARY	p1	ref	IX_1	IX_1	8	--	1	100	Using where; Using index
3	DEPENDENT SUBQUERY	p2	ref	IX_1	IX_1	8	--	1	100	Using where; Using index

So it’s a pretty nonsensical query anyway but why is the ORDER BY making the table elimination impossible?

Cheers,

Rhys

-----Original Message-----
From: Sergey Petrunia [mailto:sergey@mariadb.com]
Sent: 22 June 2015 14:16
To: Rhys Campbell
Cc: maria-discuss@lists.launchpad.net
Subject: Re: [Maria-discuss] View missing from EXPLAIN after ORDER BY removed from definition

On Mon, Jun 22, 2015 at 11:24:39AM +0000, Rhys Campbell wrote:

> Hello All,

> I had an interesting problem presented to me this morning. A seemingly simple query was running for ages and filling up /tmp causing MariaDB to crash. Now, this isn't perhaps the best designed database but the query didn't seem to be that bad. T1 is a table but t2 is a view (modified definitions provided below)

> SELECT SQL_NO_CACHE p.col1

> FROM t1 p

> LEFT OUTER JOIN t2 ot

> ON p.col1 = ot.col2

> LIMIT 500;

So, ther query

* uses LEFT JOIN

* Doesn't use any columns from table 'ot' except in the ON clause.

* The ON clause has a restriction in form ot.col2=...

Does ot.col2 refer to the primary key of the VIEW's underlying table?

If yes,

then the query satisifies conditions for the Table Elimination optimization.

Table Elimination removes t2 and its attached subquery from the query plan.

If not,

could you try the query after set optimizer_switch='table_elimination=off',

and/or post the output of EXPLAIN EXTENDED; SHOW WARNINGS?

> To rectify this I modified an index that was use by the view (note the

> key_len = 8 in above, went to 106 with the new index when EXPLAINed

> separately.). I also removed the ORDER BY from the view. After this the query

> performance much better returning in ~ 250 milliseconds. Removal of the ORDER

> By was primarily responsible for the performance improvement.

Sergei

Sergei Petrunia, Software Developer

MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog