date:20100217

[Maria-developers] Rev 2759: Subquery optimizations backport: in file:///home/psergey/dev/maria-5.3-subqueries-r7/

2010-02-17 Thread Sergey Petrunya

At file:///home/psergey/dev/maria-5.3-subqueries-r7/


revno: 2759
revision-id: pser...@askmonty.org-20100217100527-k81b7torhmj99moy
parent: pser...@askmonty.org-20100215215306-hc0levm9ag1lv1b1
committer: Sergey Petrunya pser...@askmonty.org
branch nick: maria-5.3-subqueries-r7
timestamp: Wed 2010-02-17 13:05:27 +0300
message:
  Subquery optimizations backport:
  - Update test results
  - More comments
  - Add Item_in_optimizer::transform() which was lost in backport
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2010-01-17 20:52:20 +
+++ b/mysql-test/r/subselect.result 2010-02-17 10:05:27 +
@@ -1377,7 +1377,7 @@
 2  DEPENDENT SUBQUERY  t1  index_subquery  a   a   5   
func1001100.00  Using index; Using where
 Warnings:
 Note   1003select `test`.`t2`.`a` AS `a` from `test`.`t2` where 
in_optimizer(`test`.`t2`.`a`,exists(index_lookup(cache(`test`.`t2`.`a`) 
in t1 on a where ((`test`.`t1`.`b`  30) and (cache(`test`.`t2`.`a`) = 
`test`.`t1`.`a`)
-drop table t1, t2, t3;
+drop table t0, t1, t2, t3;
 create table t1 (a int, b int);
 create table t2 (a int, b int);
 create table t3 (a int, b int);

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result2010-01-17 14:51:10 +
+++ b/mysql-test/r/subselect3.result2010-02-17 10:05:27 +
@@ -873,7 +873,7 @@
 Note   1276Field or reference 'test.t1.a' of SELECT #3 was resolved in 
SELECT #2
 Note   1276Field or reference 'test.t1.c' of SELECT #3 was resolved in 
SELECT #2
 Error  1054Unknown column 'c' in 'field list'
-Note   1003select `c` AS `c` from (select (select count(`test`.`t1`.`a`) 
AS `COUNT(a)` from dual group by `c`) AS `(SELECT COUNT(a) FROM 
+Note   1003select `c` AS `c` from (select (select count(`test`.`t1`.`a`) 
AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from 
`test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM 
 (SELECT COUNT(b) FROM t1) AS x GROUP BY c
 )` from `test`.`t1` group by `test`.`t1`.`b`) `y`
 DROP TABLE t1;
@@ -1117,7 +1117,7 @@
 explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp120
 and t4.pk=t1.c);
 id select_type table   typepossible_keys   key key_len ref 
rowsExtra
-1  PRIMARY t1  range   kp1 kp1 5   NULL48  Using 
index condition; Using where; Using MRR; LooseScan
+1  PRIMARY t1  range   kp1 kp1 5   NULL48  Using 
index condition; Using MRR; LooseScan
 1  PRIMARY t4  eq_ref  PRIMARY PRIMARY 4   test.t1.c   1   
Using index; FirstMatch(t1)
 1  PRIMARY t3  ALL NULLNULLNULLNULL100 Using 
where; Using join buffer
 drop table t1, t3, t4;

=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result2010-01-17 14:51:10 +
+++ b/mysql-test/r/subselect4.result2010-02-17 10:05:27 +
@@ -13,9 +13,9 @@
 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
 ORDER BY count(*);
 id select_type table   typepossible_keys   key key_len ref 
rowsExtra
-1  PRIMARY t1  index   NULLa   5   NULL2   Using 
index; Using temporary
+1  PRIMARY t1  index   NULLa   5   NULL2   Using 
where; Using index; Using temporary
 2  DEPENDENT SUBQUERY  t2  ALL NULLNULLNULLNULL
2   Using where
-3  DEPENDENT SUBQUERY  NULLNULLNULLNULLNULLNULL
NULLno matching row in const table
+3  DEPENDENT SUBQUERY  t3  system  NULLNULLNULLNULL
0   const row not found
 # should not crash the next statement
 SELECT 1 FROM t1
 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
@@ -77,10 +77,10 @@
 EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ;
 id select_type table   typepossible_keys   key key_len ref 
rowsfilteredExtra
 1  PRIMARY t1  system  NULLNULLNULLNULL1   100.00  
-2  DEPENDENT SUBQUERY  NULLNULLNULLNULLNULLNULL
NULLNULLImpossible WHERE noticed after reading const tables
+2  DEPENDENT SUBQUERY  t2  ref d   d   5   const   
1   100.00  Using index
 Warnings:
 Note   1276Field or reference 'test.t1.c' of SELECT #2 was resolved in 
SELECT #1
-Note   1003select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` 
= '0')) AS `RESULT` from dual
+Note   1003select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` 
= '0')) AS `RESULT` from `test`.`t1`
 first equivalent variant
 SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY 
c ;
 RESULT
@@ -88,10 +88,10 @@
 EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d =

[Maria-developers] Rev 2760: Subquery backport: in file:///home/psergey/dev/maria-5.3-subqueries-r7/

2010-02-17 Thread Sergey Petrunya

At file:///home/psergey/dev/maria-5.3-subqueries-r7/


revno: 2760
revision-id: pser...@askmonty.org-20100217104755-3psvc5fmo3pqsnpy
parent: pser...@askmonty.org-20100217100527-k81b7torhmj99moy
committer: Sergey Petrunya pser...@askmonty.org
branch nick: maria-5.3-subqueries-r7
timestamp: Wed 2010-02-17 13:47:55 +0300
message:
  Subquery backport:
  - More test results updates (checked)
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result   2010-02-11 21:56:02 +
+++ b/mysql-test/r/subselect3_jcl6.result   2010-02-17 10:47:55 +
@@ -877,7 +877,7 @@
 Note   1276Field or reference 'test.t1.a' of SELECT #3 was resolved in 
SELECT #2
 Note   1276Field or reference 'test.t1.c' of SELECT #3 was resolved in 
SELECT #2
 Error  1054Unknown column 'c' in 'field list'
-Note   1003select `c` AS `c` from (select (select count(`test`.`t1`.`a`) 
AS `COUNT(a)` from dual group by `c`) AS `(SELECT COUNT(a) FROM 
+Note   1003select `c` AS `c` from (select (select count(`test`.`t1`.`a`) 
AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from 
`test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM 
 (SELECT COUNT(b) FROM t1) AS x GROUP BY c
 )` from `test`.`t1` group by `test`.`t1`.`b`) `y`
 DROP TABLE t1;
@@ -1122,7 +1122,7 @@
 explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp120
 and t4.pk=t1.c);
 id select_type table   typepossible_keys   key key_len ref 
rowsExtra
-1  PRIMARY t1  range   kp1 kp1 5   NULL48  Using 
index condition; Using where; Using MRR; LooseScan
+1  PRIMARY t1  range   kp1 kp1 5   NULL48  Using 
index condition; Using MRR; LooseScan
 1  PRIMARY t4  eq_ref  PRIMARY PRIMARY 4   test.t1.c   1   
Using index; FirstMatch(t1)
 1  PRIMARY t3  ALL NULLNULLNULLNULL100 Using 
where; Using join buffer
 drop table t1, t3, t4;

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2010-01-17 20:52:20 +
+++ b/mysql-test/r/subselect_no_semijoin.result 2010-02-17 10:47:55 +
@@ -54,7 +54,7 @@
 Warnings:
 Note   1276Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
 Note   1276Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note   1003select 1 AS `1` from dual having ((select '1' AS `a`) = 1)
+Note   1003select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' 
AS `a`) = 1)
 SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 1
 1
@@ -207,7 +207,7 @@
 3  DERIVED t2  ALL NULLNULLNULLNULL2   100.00  
Using where
 2  SUBQUERYt3  ALL NULLNULLNULLNULL3   
100.00  Using where; Using filesort
 Warnings:
-Note   1003select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where 
(`test`.`t3`.`a`  8) order by 1 desc limit 1) AS `(select t3.a from t3 where 
a8 order by 1 desc limit 1)`,'2' AS `a` from dual
+Note   1003select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where 
(`test`.`t3`.`a`  8) order by 1 desc limit 1) AS `(select t3.a from t3 where 
a8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS 
`a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a`  1)) `tt`
 select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) 
from t3) order by 1 desc limit 1);
 a
 2
@@ -318,7 +318,7 @@
 Warnings:
 Note   1276Field or reference 'test.t2.a' of SELECT #2 was resolved in 
SELECT #1
 Note   1276Field or reference 'test.t2.a' of SELECT #3 was resolved in 
SELECT #1
-Note   1003select (select '2' AS `a` from dual where ('2' = 
`test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where 
(`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a 
union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
+Note   1003select (select '2' AS `a` from `test`.`t1` where ('2' = 
`test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where 
(`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a 
union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
 select (select a from t1 where t1.a=t2.a union all select a from t5 where 
t5.a=t2.a), a from t2;
 ERROR 21000: Subquery returns more than 1 row
 create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
@@ -739,7 +739,7 @@
 Warnings:
 Note   1249Select 3 was reduced during optimization
 Note   1249Select 2 was reduced during optimization
-Note   1003select `test`.`t2`.`id` AS `id` from `test`.`t2` where 
(`test`.`t2`.`id` = cache((1 + 1)))
+Note   1003select `test`.`t2`.`id` AS `id` from `test`.`t2` where 
(`test`.`t2`.`id` = (1 + 1))
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN

[Maria-developers] Rev 2818: Increased loop counts of sql-bench tests to get run times around in file:///Users/hakan/work/monty_program/maria/

2010-02-17 Thread Hakan Kuecuekyilmaz

At file:///Users/hakan/work/monty_program/maria/


revno: 2818
revision-id: ha...@askmonty.org-20100217201002-gax8y3ts7yf6u50a
parent: mo...@askmonty.org-20100212142113-wdv50xx19quursaf
committer: Hakan Kuecuekyilmaz ha...@askmonty.org
branch nick: maria
timestamp: Wed 2010-02-17 21:10:02 +0100
message:
  Increased loop counts of sql-bench tests to get run times around
  5 minutes on current machines. Tested on a Xeon machine and a new dual core 
laptop.
=== modified file 'sql-bench/test-ATIS.sh'
--- a/sql-bench/test-ATIS.sh2009-05-29 13:40:55 +
+++ b/sql-bench/test-ATIS.sh2010-02-17 20:10:02 +
@@ -28,7 +28,7 @@
 use DBI;
 use Benchmark;
 
-$opt_loop_count=100;   # Run selects this many times
+$opt_loop_count=5000;  # Run selects this many times
 
 $pwd = cwd(); $pwd = . if ($pwd eq '');
 require $pwd/bench-init.pl || die Can't read Configuration file: $!\n;

=== modified file 'sql-bench/test-alter-table.sh'
--- a/sql-bench/test-alter-table.sh 2009-05-29 13:40:55 +
+++ b/sql-bench/test-alter-table.sh 2010-02-17 20:10:02 +
@@ -25,7 +25,7 @@
 use Benchmark;
 
 $opt_start_field_count=8;  # start with this many fields
-$opt_loop_count=100;   # How many tests to do
+$opt_loop_count=1; # How many tests to do
 $opt_row_count=1000;   # Rows in the table
 $opt_field_count=1000; # Add until this many fields.
 $opt_time_limit=10*60; # Don't wait more than 10 min for some tests

=== modified file 'sql-bench/test-big-tables.sh'
--- a/sql-bench/test-big-tables.sh  2009-05-29 13:40:55 +
+++ b/sql-bench/test-big-tables.sh  2010-02-17 20:10:02 +
@@ -25,7 +25,7 @@
 use DBI;
 use Benchmark;
 
-$opt_loop_count=1000; # Change this to make test harder/easier
+$opt_loop_count=7; # Change this to make test harder/easier
 $opt_field_count=1000;
 
 $pwd = cwd(); $pwd = . if ($pwd eq '');

=== modified file 'sql-bench/test-connect.sh'
--- a/sql-bench/test-connect.sh 2010-02-10 21:26:06 +
+++ b/sql-bench/test-connect.sh 2010-02-17 20:10:02 +
@@ -28,7 +28,7 @@
 use DBI;
 use Benchmark;
 
-$opt_loop_count=10;# Change this to make test harder/easier
+$opt_loop_count=50;# Change this to make test harder/easier
 $str_length=65000; # This is the length of blob strings in PART:5
 $max_test=20;  # How many times to test if the server is busy
 

=== modified file 'sql-bench/test-select.sh'
--- a/sql-bench/test-select.sh  2009-05-29 13:40:55 +
+++ b/sql-bench/test-select.sh  2010-02-17 20:10:02 +
@@ -26,7 +26,7 @@
 use Benchmark;
 
 $opt_loop_count=1;
-$opt_medium_loop_count=1000;
+$opt_medium_loop_count=7000;
 $opt_small_loop_count=10;
 $opt_regions=6;
 $opt_groups=100;

=== modified file 'sql-bench/test-transactions.sh'
--- a/sql-bench/test-transactions.sh2009-05-29 13:40:55 +
+++ b/sql-bench/test-transactions.sh2010-02-17 20:10:02 +
@@ -28,8 +28,8 @@
 
 $opt_groups=27;# Characters are 'A' - Z
 
-$opt_loop_count=1; # Change this to make test harder/easier
-$opt_medium_loop_count=100; # Change this to make test harder/easier
+$opt_loop_count=50;# Change this to make test harder/easier
+$opt_medium_loop_count=1; # Change this to make test harder/easier
 
 $pwd = cwd(); $pwd = . if ($pwd eq '');
 require $pwd/bench-init.pl || die Can't read Configuration file: $!\n;

=== modified file 'sql-bench/test-wisconsin.sh'
--- a/sql-bench/test-wisconsin.sh   2009-05-29 13:40:55 +
+++ b/sql-bench/test-wisconsin.sh   2010-02-17 20:10:02 +
@@ -21,7 +21,7 @@
 use DBI;
 use Benchmark;
 
-$opt_loop_count=10;
+$opt_loop_count=5000;
 
 $pwd = cwd(); $pwd = . if ($pwd eq '');
 require $pwd/bench-init.pl || die Can't read Configuration file: $!\n;

___
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

[Maria-developers] LIMIT optimisations

2010-02-17 Thread Jocelyn Fournier


Hi,

Following this discussion in 2007 : http://lists.mysql.com/internals/34287

Is there any plan to implement such an optimisation in MariaDB ? (I 
think a lot of web app using pagination could take benefit of such an 
optimisation, although there are some workarounds to avoid big LIMIT for 
pagination)


Thanks !
  Jocelyn

___
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

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2818: Increased loop counts of sql-bench tests to get run times around

2010-02-17 Thread noreply


revno: 2818
committer: Hakan Kuecuekyilmaz ha...@askmonty.org
branch nick: maria
timestamp: Wed 2010-02-17 21:10:02 +0100
message:
  Increased loop counts of sql-bench tests to get run times around
  5 minutes on current machines. Tested on a Xeon machine and a new dual core 
laptop.
modified:
  sql-bench/test-ATIS.sh
  sql-bench/test-alter-table.sh
  sql-bench/test-big-tables.sh
  sql-bench/test-connect.sh
  sql-bench/test-select.sh
  sql-bench/test-transactions.sh
  sql-bench/test-wisconsin.sh


--
lp:maria
https://code.launchpad.net/~maria-captains/maria/5.1

Your team Maria developers is subscribed to branch lp:maria.
To unsubscribe from this branch go to 
https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.
=== modified file 'sql-bench/test-ATIS.sh'
--- sql-bench/test-ATIS.sh	2009-05-29 13:40:55 +
+++ sql-bench/test-ATIS.sh	2010-02-17 20:10:02 +
@@ -28,7 +28,7 @@
 use DBI;
 use Benchmark;
 
-$opt_loop_count=100;		# Run selects this many times
+$opt_loop_count=5000;		# Run selects this many times
 
 $pwd = cwd(); $pwd = . if ($pwd eq '');
 require $pwd/bench-init.pl || die Can't read Configuration file: $!\n;

=== modified file 'sql-bench/test-alter-table.sh'
--- sql-bench/test-alter-table.sh	2009-05-29 13:40:55 +
+++ sql-bench/test-alter-table.sh	2010-02-17 20:10:02 +
@@ -25,7 +25,7 @@
 use Benchmark;
 
 $opt_start_field_count=8;	# start with this many fields
-$opt_loop_count=100;		# How many tests to do
+$opt_loop_count=1;		# How many tests to do
 $opt_row_count=1000; 		# Rows in the table
 $opt_field_count=1000;		# Add until this many fields.
 $opt_time_limit=10*60;		# Don't wait more than 10 min for some tests

=== modified file 'sql-bench/test-big-tables.sh'
--- sql-bench/test-big-tables.sh	2009-05-29 13:40:55 +
+++ sql-bench/test-big-tables.sh	2010-02-17 20:10:02 +
@@ -25,7 +25,7 @@
 use DBI;
 use Benchmark;
 
-$opt_loop_count=1000; # Change this to make test harder/easier
+$opt_loop_count=7; # Change this to make test harder/easier
 $opt_field_count=1000;
 
 $pwd = cwd(); $pwd = . if ($pwd eq '');

=== modified file 'sql-bench/test-connect.sh'
--- sql-bench/test-connect.sh	2010-02-10 21:26:06 +
+++ sql-bench/test-connect.sh	2010-02-17 20:10:02 +
@@ -28,7 +28,7 @@
 use DBI;
 use Benchmark;
 
-$opt_loop_count=10;	# Change this to make test harder/easier
+$opt_loop_count=50;	# Change this to make test harder/easier
 $str_length=65000;	# This is the length of blob strings in PART:5
 $max_test=20;		# How many times to test if the server is busy
 

=== modified file 'sql-bench/test-select.sh'
--- sql-bench/test-select.sh	2009-05-29 13:40:55 +
+++ sql-bench/test-select.sh	2010-02-17 20:10:02 +
@@ -26,7 +26,7 @@
 use Benchmark;
 
 $opt_loop_count=1;
-$opt_medium_loop_count=1000;
+$opt_medium_loop_count=7000;
 $opt_small_loop_count=10;
 $opt_regions=6;
 $opt_groups=100;

=== modified file 'sql-bench/test-transactions.sh'
--- sql-bench/test-transactions.sh	2009-05-29 13:40:55 +
+++ sql-bench/test-transactions.sh	2010-02-17 20:10:02 +
@@ -28,8 +28,8 @@
 
 $opt_groups=27;		# Characters are 'A' - Z
 
-$opt_loop_count=1;	# Change this to make test harder/easier
-$opt_medium_loop_count=100; # Change this to make test harder/easier
+$opt_loop_count=50;	# Change this to make test harder/easier
+$opt_medium_loop_count=1; # Change this to make test harder/easier
 
 $pwd = cwd(); $pwd = . if ($pwd eq '');
 require $pwd/bench-init.pl || die Can't read Configuration file: $!\n;

=== modified file 'sql-bench/test-wisconsin.sh'
--- sql-bench/test-wisconsin.sh	2009-05-29 13:40:55 +
+++ sql-bench/test-wisconsin.sh	2010-02-17 20:10:02 +
@@ -21,7 +21,7 @@
 use DBI;
 use Benchmark;
 
-$opt_loop_count=10;
+$opt_loop_count=5000;
 
 $pwd = cwd(); $pwd = . if ($pwd eq '');
 require $pwd/bench-init.pl || die Can't read Configuration file: $!\n;

___
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

[Maria-developers] Rev 2759: Subquery optimizations backport: in file:///home/psergey/dev/maria-5.3-subqueries-r7/

[Maria-developers] Rev 2760: Subquery backport: in file:///home/psergey/dev/maria-5.3-subqueries-r7/

[Maria-developers] Rev 2818: Increased loop counts of sql-bench tests to get run times around in file:///Users/hakan/work/monty_program/maria/

[Maria-developers] LIMIT optimisations

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2818: Increased loop counts of sql-bench tests to get run times around

5 matches

Site Navigation

Mail list logo

Footer information