On Tue, Mar 27, 2018 at 04:48:10PM +0530, Varun wrote:
> revision-id: 167a96b5157049408a6ad4bca7abcd376af93fb5 
> (mariadb-10.3.0-644-g167a96b5157)
> parent(s): 4359c6b4806605c78987e50cab3a6b42016b7603
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2018-03-27 16:45:46 +0530
> message:
> 
> MDEV-9959: A serious MariaDB server performance bug
> 
> step#1: if a derived table has SELECT DISTINCT, provide index statistics for 
> it so that the join optimizer in the
> upper select knows that ref access to the table will produce one row.
> 
> Added handling for multiple selects in the derived table

...
> diff --git a/mysql-test/r/mdev9959.result b/mysql-test/r/mdev9959.result
> new file mode 100644
> index 00000000000..049e0350cca
> --- /dev/null
> +++ b/mysql-test/r/mdev9959.result
> @@ -0,0 +1,46 @@
> +create table t1(a int);
> +insert into t1 values (1),(2),(3),(4),(5),(6);
> +create table t2(a int, b int,c int);
> +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5);
> +create table t3(a int, b int);
> +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2);
> +table "<derived2>" should have type=ref and rows=1
> +one select in derived table
> +with distinct
> +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q  
> where t1.a=q.a;
> +id   select_type     table   type    possible_keys   key     key_len ref     
> rows    r_rows  filtered        r_filtered      Extra
> +1    PRIMARY t1      ALL     NULL    NULL    NULL    NULL    6       6.00    
> 100.00  100.00  Using where
> +1    PRIMARY <derived2>      ref     key0    key0    5       test.t1.a       
> 1       1.00    100.00  100.00  
> +2    DERIVED t2      ALL     NULL    NULL    NULL    NULL    6       6.00    
> 100.00  100.00  Using temporary; Using filesort
> +# multiple selects in derived table
> +# NO UNION ALL
> +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union  
> (select t2.a,t2.b from t2 order by c))q  where t1.a=q.a;
> +id   select_type     table   type    possible_keys   key     key_len ref     
> rows    r_rows  filtered        r_filtered      Extra
> +1    PRIMARY t1      ALL     NULL    NULL    NULL    NULL    6       6.00    
> 100.00  100.00  Using where
> +1    PRIMARY <derived2>      ref     key0    key0    5       test.t1.a       
> 1       1.00    100.00  100.00  
> +2    DERIVED t2      ALL     NULL    NULL    NULL    NULL    6       6.00    
> 100.00  100.00  
> +3    UNION   t2      ALL     NULL    NULL    NULL    NULL    6       6.00    
> 100.00  100.00  
> +NULL UNION RESULT    <union2,3>      ALL     NULL    NULL    NULL    NULL    
> NULL    6.00    NULL    NULL    

How would you explain the above? The derived table is a union of two selects,
each of which produces 6 rows. Neither of the selects have DISTINCT attribute.

Why does the optimization fire and set ref=1 in this case?

(if both parts of UNION had distinct, this would make sense. But if neither
does, I don't see any logic)

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog



_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to     : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Reply via email to