maria-discuss team mailing list archive

[Rhys Campbell <Rhys.Campbell@xxxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxx]
Sent: 22 June 2015 14:16
To: Rhys Campbell
Cc: maria-discuss@xxxxxxxxxxxxxxxxxxx
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.



BR

Sergei

--

Sergei Petrunia, Software Developer

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