Fix of LEFT JOIN optimizer bug, problem with key cache on Windows and

a lot of portability issues.


Docs/manual.texi:
  Changed FOREIGN KEY to FOREIGN KEY constraint
client/mysqladmin.c:
  Don't flush MASTER or SLAVE on refresh
configure.in:
  Fix for hpux 11.0
extra/perror.c:
  New error message
include/my_pthread.h:
  Portability fix for windows
myisam/mi_locking.c:
  Ensure that locking doesn't interfere with pread/pwrite on windows
myisam/sort.c:
  checked with purecover
mysys/mf_tempfile.c:
  Fix for windows
mysys/my_lock.c:
  Ensure that locking doesn't interfere with pread/pwrite on windows
mysys/my_winthread.c:
  Portability fix
sql-bench/Results/ATIS-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/RUN-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/alter-table-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/big-tables-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/connect-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/create-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/insert-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/select-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/wisconsin-mysql-NT_4.0:
  New benchmark runs
sql-bench/crash-me.sh:
  Fixed things for PostgreSQL
sql-bench/limits/mysql-3.23.cfg:
  Update for new crash-me
sql-bench/limits/mysql.cfg:
  Update for new crash-me
sql-bench/print-limit-table:
  Fixed position for alter table rename
sql-bench/test-insert.sh:
  Fix for PostgreSQL
sql/field.cc:
  Fix for default values in CREATE ... SELECT
sql/field.h:
  Fix for default values in CREATE ... SELECT
sql/log.cc:
  Fixed typo
sql/log_event.cc:
  Portability fix
sql/mysqlbinlog.cc:
  Portability fix
sql/mysqld.cc:
  Don't turn of concurrent insert with --skip-new or --safe
sql/sql_base.cc:
  Portability fix
sql/sql_class.cc:
  Portability fix
sql/sql_class.h:
  Portability fix
sql/sql_parse.cc:
  Fix for --log-slow-queries
sql/sql_repl.cc:
  Portability fixes
sql/sql_select.cc:
  Fixed optimizer bug for LEFT JOIN
sql/sql_select.h:
  Fixed optimizer bug for LEFT JOIN
sql/sql_table.cc:
  Fix for default values in CREATE ... SELECT
sql/sql_yacc.yy:
  Added optional AS to: CREATE TABLE foo [ AS ] SELECT ...

1 files changed, 40 insertions, 35 deletions

diff --git a/sql-bench/Results/insert-mysql-NT_4.0 b/sql-bench/Results/insert-mysql-NT_4.0
index bb1d1e69ba8..ce42b79436e 100644
--- a/sql-bench/Results/insert-mysql-NT_4.0
+++ b/sql-bench/Results/insert-mysql-NT_4.0
@@ -1,4 +1,4 @@
-Testing server 'MySQL 3.23.25 beta' at 2000-09-27  2:59:03

+Testing server 'MySQL 3.23.26 gamma' at 2000-10-13 14:20:19

 

 Testing the speed of inserting data into 1 table and do some selects on it.

 The tests are done with a table that has 100000 rows.

@@ -8,67 +8,72 @@ Creating tables
 Inserting 100000 rows in order

 Inserting 100000 rows in reverse order

 Inserting 100000 rows in random order

-Time for insert (300000): 203 wallclock secs (30.05 usr 40.38 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for insert (300000): 221 wallclock secs (30.06 usr 39.92 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Testing insert of duplicates

-Time for insert_duplicates (300000): 59 wallclock secs ( 6.50 usr 10.52 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for insert_duplicates (100000): 59 wallclock secs ( 8.11 usr 13.67 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Retrieving data from the table

-Time for select_big (10:3000000): 88 wallclock secs (64.08 usr 24.75 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for order_by_key (10:3000000): 89 wallclock secs (63.61 usr 25.09 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for order_by (10:3000000): 99 wallclock secs (63.39 usr 25.81 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for select_diff_key (500:1000): 123 wallclock secs ( 0.39 usr  0.08 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for select_range_prefix (5010:42084): 15 wallclock secs ( 5.30 usr  1.48 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for select_range_key2 (5010:42084): 16 wallclock secs ( 4.69 usr  1.74 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for select_key_prefix (200000): 198 wallclock secs (91.75 usr 40.53 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for select_key (200000): 193 wallclock secs (92.27 usr 38.89 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for select_key2 (200000): 202 wallclock secs (93.95 usr 37.08 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_big (10:3000000): 89 wallclock secs (63.69 usr 25.58 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for order_by_key (10:3000000): 90 wallclock secs (64.05 usr 25.08 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for order_by (10:3000000): 98 wallclock secs (63.58 usr 25.14 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_diff_key (500:1000): 124 wallclock secs ( 0.41 usr  0.09 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_range_prefix (5010:42084): 14 wallclock secs ( 4.47 usr  1.89 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_range_key2 (5010:42084): 16 wallclock secs ( 4.64 usr  1.77 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_key_prefix (200000): 201 wallclock secs (91.41 usr 40.78 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_key (200000): 196 wallclock secs (96.84 usr 36.63 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_key2 (200000): 203 wallclock secs (92.92 usr 39.80 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Test of compares with simple ranges

-Time for select_range_prefix (20000:43500): 13 wallclock secs ( 5.44 usr  2.44 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for select_range_key2 (20000:43500): 13 wallclock secs ( 5.33 usr  2.16 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for select_group (111): 40 wallclock secs ( 0.13 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for min_max_on_key (15000): 14 wallclock secs ( 6.51 usr  2.50 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for min_max (60): 20 wallclock secs ( 0.05 usr  0.03 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for count_on_key (100): 36 wallclock secs ( 0.05 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for count (100): 39 wallclock secs ( 0.06 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for count_distinct_big (20): 92 wallclock secs ( 0.05 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_range_prefix (20000:43500): 12 wallclock secs ( 5.34 usr  2.47 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_range_key2 (20000:43500): 14 wallclock secs ( 5.92 usr  1.95 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for select_group (111): 36 wallclock secs ( 0.08 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for min_max_on_key (15000): 14 wallclock secs ( 6.45 usr  2.63 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for min_max (60): 18 wallclock secs ( 0.11 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for count_on_key (100): 36 wallclock secs ( 0.03 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for count (100): 34 wallclock secs ( 0.11 usr  0.03 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for count_distinct_big (20): 96 wallclock secs ( 0.01 usr  0.03 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Testing update of keys with functions

-Time for update_of_key (500):  62 wallclock secs ( 4.77 usr  6.50 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for update_of_key_big (501): 34 wallclock secs ( 0.08 usr  0.05 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for update_of_key (50000):  56 wallclock secs ( 4.45 usr  7.47 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for update_of_key_big (501): 33 wallclock secs ( 0.06 usr  0.03 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Testing update with key

-Time for update_with_key (100000):  185 wallclock secs (24.97 usr 40.99 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for update_with_key (300000):  188 wallclock secs (25.03 usr 40.83 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Testing update of all rows

-Time for update_big (500):  64 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for update_big (10):  62 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Testing left outer join

 Time for outer_join_on_key (10:10):  40 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for outer_join (10:10):  120 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for outer_join_found (10:10):  106 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for outer_join_not_found (500:10):  55 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for outer_join (10:10):  118 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for outer_join_found (10:10):  105 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for outer_join_not_found (500:10):  56 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+

+Testing INSERT INTO ... SELECT

+Time for insert_select_1_key (1):   8 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for insert_select_2_keys (1):  13 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for drop table(2):  0 wallclock secs ( 0.02 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Testing delete

-Time for delete_key (10000):  7 wallclock secs ( 0.97 usr  1.25 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

-Time for delete_all (12): 22 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for delete_key (10000):  7 wallclock secs ( 0.97 usr  1.36 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for delete_all (12): 22 wallclock secs ( 0.00 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Insert into table with 16 keys and with a primary key with 16 parts

-Time for insert_key (100000): 1593 wallclock secs (14.31 usr 14.47 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for insert_key (100000): 1447 wallclock secs (13.78 usr 13.42 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Testing update of keys

-Time for update_of_key (256): 479 wallclock secs ( 0.06 usr  0.06 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for update_of_key (256): 436 wallclock secs ( 0.01 usr  0.08 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Deleting rows from the table

-Time for delete_big_many_keys (128): 1498 wallclock secs ( 0.02 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for delete_big_many_keys (128): 1422 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Deleting everything from table

-Time for delete_all_many_keys (1): 1498 wallclock secs ( 0.02 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for delete_all_many_keys (1): 1422 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Inserting 100000 rows with multiple values

-Time for multiple_value_insert (100000):  9 wallclock secs ( 2.51 usr  0.24 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Time for multiple_value_insert (100000):  9 wallclock secs ( 2.34 usr  0.26 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

 Time for drop table(1):  0 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

 

-Total time: 5829 wallclock secs (581.28 usr 317.09 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

+Total time: 5599 wallclock secs (584.97 usr 320.95 sys +  0.00 cusr  0.00 csys =  0.00 CPU)