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.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog