maria-discuss team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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