Below is the list of changes that have just been committed into a local
5.0 repository of monty. When monty does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.1933 05/05/13 12:08:08 monty@stripped +29 -0
concurrent-insert can now be set to 2 for concurrent inserts when there is holes in the data file
myisam_max_extra_sort_file_size is depricated
Ensure that myisam_data_pointer_size is honoured when creating new MyISAM files
Changed default value of myisam_data_pointer_size from 4 to 6 to get rid of 'table-is-full' errors
sql/set_var.cc
1.110 05/05/13 12:08:03 monty@stripped +2 -16
concurrent-insert is now an integer, not a bool
myisam_max_extra_sort_file_size is deleted
sql/mysqld.cc
1.466 05/05/13 12:08:03 monty@stripped +14 -8
concurrent-insert can now be set to 2 for concurrent inserts when there is holes in the data file
myisam_max_extra_sort_file_size is depricated
mysys/thr_lock.c
1.41 05/05/13 12:08:03 monty@stripped +18 -10
Extra paramter to get_status to signal if concurrent_insert was used
mysql-test/t/view.test
1.68 05/05/13 12:08:03 monty@stripped +1 -1
Drop tables that may be left from previous tests
mysql-test/t/variables.test
1.39 05/05/13 12:08:03 monty@stripped +3 -11
concurrent_insert is now a integer, not a boolean
myisam_extra_sort_file_size is deleted
mysql-test/t/ps_1general.test
1.23 05/05/13 12:08:03 monty@stripped +1 -1
Drop test table
mysql-test/t/myisam.test
1.43 05/05/13 12:08:03 monty@stripped +54 -0
Test of concurrent_insert=2
mysql-test/t/index_merge.test
1.9 05/05/13 12:08:03 monty@stripped +1 -1
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/view.result
1.79 05/05/13 12:08:03 monty@stripped +2 -2
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/variables.result
1.63 05/05/13 12:08:03 monty@stripped +8 -19
concurrent_insert is now a integer, not a boolean
myisam_extra_sort_file_size is deleted
mysql-test/r/range.result
1.34 05/05/13 12:08:03 monty@stripped +4 -4
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/ps_1general.result
1.40 05/05/13 12:08:03 monty@stripped +3 -3
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/preload.result
1.9 05/05/13 12:08:03 monty@stripped +18 -18
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/null.result
1.32 05/05/13 12:08:03 monty@stripped +2 -2
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/myisam.result
1.61 05/05/13 12:08:03 monty@stripped +57 -0
Test of concurrent_insert=2
mysql-test/r/merge.result
1.39 05/05/13 12:08:03 monty@stripped +1 -1
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/index_merge_ror.result
1.6 05/05/13 12:08:03 monty@stripped +27 -27
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/index_merge.result
1.14 05/05/13 12:08:02 monty@stripped +13 -13
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/group_min_max.result
1.9 05/05/13 12:08:02 monty@stripped +13 -13
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
mysql-test/r/gis-rtree.result
1.13 05/05/13 12:08:02 monty@stripped +1 -1
Test result changed because default value for myisam_data_pointer_size is changed from 4 -> 6
myisam/myisamdef.h
1.76 05/05/13 12:08:02 monty@stripped +2 -1
Support for insert-at-end even if there is holes in data file
myisam/mi_write.c
1.52 05/05/13 12:08:02 monty@stripped +2 -1
If 'append_insert_at_end' is set, only write at end of myisam record file
myisam/mi_statrec.c
1.11 05/05/13 12:08:02 monty@stripped +2 -1
If 'append_insert_at_end' is set, only write at end of myisam record file
myisam/mi_static.c
1.17 05/05/13 12:08:02 monty@stripped +3 -4
Change behavior of myisam_concurrent_insert so that setting this to 2 allows inserts even if there is a hole in the data file.
Default value is 2 for MyISAM direct usage but will be set to 1 (old default) by mysqld.cc
myisam/mi_locking.c
1.39 05/05/13 12:08:02 monty@stripped +38 -7
Add extra argument to 'mi_get_status' to allow thr_lock to signal that we want to do a concurrent insert
If this is used, we will append new insert rows at end of data file.
Change mi_check_status() to allow concurrent_inserts even if there are holes in the file when myisam_concurent_insert=2
myisam/mi_dynrec.c
1.39 05/05/13 12:08:02 monty@stripped +8 -4
If 'append_insert_at_end' is set, only write at end of myisam record file
myisam/mi_create.c
1.46 05/05/13 12:08:02 monty@stripped +3 -4
Ensure that myisam_data_pointer_size is honoured
include/thr_lock.h
1.14 05/05/13 12:08:02 monty@stripped +2 -2
Added extra parameter to get_status
include/myisam.h
1.66 05/05/13 12:08:02 monty@stripped +3 -3
Change type of myisam_block_size and myisam_concurrent_insert to make them changeable in mysqld.cc
Removed not used varaible myisam_max_extra_temp_length
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: monty
# Host: mishka.local
# Root: /home/my/mysql-5.0
--- 1.65/include/myisam.h Thu Jan 6 12:59:56 2005
+++ 1.66/include/myisam.h Fri May 13 12:08:02 2005
@@ -190,10 +190,10 @@
typedef void (* invalidator_by_filename)(const char * filename);
extern my_string myisam_log_filename; /* Name of logfile */
-extern uint myisam_block_size;
+extern ulong myisam_block_size;
+extern ulong myisam_concurrent_insert;
extern my_bool myisam_flush,myisam_delay_key_write,myisam_single_user;
-extern my_bool myisam_concurrent_insert;
-extern my_off_t myisam_max_temp_length,myisam_max_extra_temp_length;
+extern my_off_t myisam_max_temp_length;
extern ulong myisam_bulk_insert_tree_size, myisam_data_pointer_size;
/* Prototypes for myisam-functions */
--- 1.13/include/thr_lock.h Sun Mar 16 18:08:58 2003
+++ 1.14/include/thr_lock.h Fri May 13 12:08:02 2005
@@ -88,10 +88,10 @@
struct st_lock_list read;
struct st_lock_list write_wait;
struct st_lock_list write;
-/* write_lock_count is incremented for write locks and reset on read locks */
+ /* write_lock_count is incremented for write locks and reset on read locks */
ulong write_lock_count;
uint read_no_write_count;
- void (*get_status)(void*); /* When one gets a lock */
+ void (*get_status)(void*, int); /* When one gets a lock */
void (*copy_status)(void*,void*);
void (*update_status)(void*); /* Before release of write */
my_bool (*check_status)(void *);
--- 1.45/myisam/mi_create.c Wed Apr 6 15:15:37 2005
+++ 1.46/myisam/mi_create.c Fri May 13 12:08:02 2005
@@ -191,11 +191,10 @@
test(test_all_bits(options, HA_OPTION_CHECKSUM | HA_PACK_RECORD));
min_pack_length+=packed;
- if (!ci->data_file_length)
+ if (!ci->data_file_length && ci->max_rows)
{
- if (ci->max_rows == 0 || pack_reclength == INT_MAX32)
- ci->data_file_length= INT_MAX32-1; /* Should be enough */
- else if ((~(ulonglong) 0)/ci->max_rows < (ulonglong) pack_reclength)
+ if (pack_reclength == INT_MAX32 ||
+ (~(ulonglong) 0)/ci->max_rows < (ulonglong) pack_reclength)
ci->data_file_length= ~(ulonglong) 0;
else
ci->data_file_length=(ulonglong) ci->max_rows*pack_reclength;
--- 1.38/myisam/mi_dynrec.c Wed Apr 13 17:06:28 2005
+++ 1.39/myisam/mi_dynrec.c Fri May 13 12:08:02 2005
@@ -149,7 +149,9 @@
{
if (_mi_find_writepos(info,reclength,&filepos,&length))
goto err;
- if (_mi_write_part_record(info,filepos,length,info->s->state.dellink,
+ if (_mi_write_part_record(info,filepos,length,
+ (info->append_insert_at_end ?
+ HA_OFFSET_ERROR : info->s->state.dellink),
(byte**) &record,&reclength,&flag))
goto err;
} while (reclength);
@@ -171,7 +173,8 @@
ulong tmp;
DBUG_ENTER("_mi_find_writepos");
- if (info->s->state.dellink != HA_OFFSET_ERROR)
+ if (info->s->state.dellink != HA_OFFSET_ERROR &&
+ !info->append_insert_at_end)
{
/* Deleted blocks exists; Get last used block */
*filepos=info->s->state.dellink;
@@ -420,8 +423,9 @@
else if (length-long_block < *reclength+4)
{ /* To short block */
if (next_filepos == HA_OFFSET_ERROR)
- next_filepos=info->s->state.dellink != HA_OFFSET_ERROR ?
- info->s->state.dellink : info->state->data_file_length;
+ next_filepos= (info->s->state.dellink != HA_OFFSET_ERROR &&
+ !info->append_insert_at_end ?
+ info->s->state.dellink : info->state->data_file_length);
if (*flag == 0) /* First block */
{
if (*reclength > MI_MAX_BLOCK_LENGTH)
--- 1.38/myisam/mi_locking.c Fri Dec 31 03:47:50 2004
+++ 1.39/myisam/mi_locking.c Fri May 13 12:08:02 2005
@@ -238,13 +238,24 @@
The following functions are called by thr_lock() in threaded applications
****************************************************************************/
-void mi_get_status(void* param)
+/*
+ Create a copy of the current status for the table
+
+ SYNOPSIS
+ mi_get_status()
+ param Pointer to Myisam handler
+ concurrent_insert Set to 1 if we are going to do concurrent inserts
+ (THR_WRITE_CONCURRENT_INSERT was used)
+*/
+
+void mi_get_status(void* param, int concurrent_insert)
{
MI_INFO *info=(MI_INFO*) param;
DBUG_ENTER("mi_get_status");
- DBUG_PRINT("info",("key_file: %ld data_file: %ld",
+ DBUG_PRINT("info",("key_file: %ld data_file: %ld concurrent_insert: %d",
(long) info->s->state.state.key_file_length,
- (long) info->s->state.state.data_file_length));
+ (long) info->s->state.state.data_file_length,
+ concurrent_insert));
#ifndef DBUG_OFF
if (info->state->key_file_length > info->s->state.state.key_file_length ||
info->state->data_file_length > info->s->state.state.data_file_length)
@@ -254,9 +265,11 @@
#endif
info->save_state=info->s->state.state;
info->state= &info->save_state;
+ info->append_insert_at_end= concurrent_insert;
DBUG_VOID_RETURN;
}
+
void mi_update_status(void* param)
{
MI_INFO *info=(MI_INFO*) param;
@@ -281,6 +294,7 @@
info->s->state.state= *info->state;
info->state= &info->s->state.state;
}
+ info->append_insert_at_end= 0;
/*
We have to flush the write cache here as other threads may start
@@ -307,20 +321,37 @@
Check if should allow concurrent inserts
IMPLEMENTATION
- Don't allow concurrent inserts if we have a hole in the table.
+ Allow concurrent inserts if we don't have a hole in the table or
+ if there is no active write lock and there is active read locks and
+ myisam_concurrent_insert == 2. In this last case the new
+ row('s) are inserted at end of file instead of filling up the hole.
+
+ The last case is to allow one to inserts into a heavily read-used table
+ even if there is holes.
NOTES
- Rtree indexes are disabled in mi_open()
+ If there is a an rtree indexes in the table, concurrent inserts are
+ disabled in mi_open()
RETURN
0 ok to use concurrent inserts
1 not ok
*/
-my_bool mi_check_status(void* param)
+my_bool mi_check_status(void *param)
{
MI_INFO *info=(MI_INFO*) param;
- return (my_bool) (info->s->state.dellink != HA_OFFSET_ERROR);
+ /*
+ The test for w_locks == 1 is here because this thread has already done an
+ external lock (in other words: w_locks == 1 means no other threads has
+ a write lock)
+ */
+ DBUG_PRINT("info",("dellink: %ld r_locks: %u w_locks: %u",
+ (long) info->s->state.dellink, (uint) info->s->r_locks,
+ (uint) info->s->w_locks));
+ return (my_bool) !(info->s->state.dellink == HA_OFFSET_ERROR ||
+ (myisam_concurrent_insert == 2 && info->s->r_locks &&
+ info->s->w_locks == 1));
}
--- 1.16/myisam/mi_static.c Wed Jun 23 13:28:58 2004
+++ 1.17/myisam/mi_static.c Fri May 13 12:08:02 2005
@@ -31,14 +31,13 @@
my_string myisam_log_filename=(char*) "myisam.log";
File myisam_log_file= -1;
uint myisam_quick_table_bits=9;
-uint myisam_block_size=MI_KEY_BLOCK_LENGTH; /* Best by test */
+ulong myisam_block_size= MI_KEY_BLOCK_LENGTH; /* Best by test */
my_bool myisam_flush=0, myisam_delay_key_write=0, myisam_single_user=0;
#if defined(THREAD) && !defined(DONT_USE_RW_LOCKS)
-my_bool myisam_concurrent_insert=1;
+ulong myisam_concurrent_insert= 2;
#else
-my_bool myisam_concurrent_insert=0;
+ulong myisam_concurrent_insert= 0;
#endif
-my_off_t myisam_max_extra_temp_length= (my_off_t)MI_MAX_TEMP_LENGTH;
my_off_t myisam_max_temp_length= MAX_FILE_SIZE;
ulong myisam_bulk_insert_tree_size=8192*1024;
ulong myisam_data_pointer_size=4;
--- 1.10/myisam/mi_statrec.c Mon Feb 2 18:25:35 2004
+++ 1.11/myisam/mi_statrec.c Fri May 13 12:08:02 2005
@@ -23,7 +23,8 @@
{
uchar temp[8]; /* max pointer length */
- if (info->s->state.dellink != HA_OFFSET_ERROR)
+ if (info->s->state.dellink != HA_OFFSET_ERROR &&
+ !info->append_insert_at_end)
{
my_off_t filepos=info->s->state.dellink;
info->rec_cache.seek_not_done=1; /* We have done a seek */
--- 1.51/myisam/mi_write.c Sat Apr 16 18:57:14 2005
+++ 1.52/myisam/mi_write.c Fri May 13 12:08:02 2005
@@ -67,7 +67,8 @@
MYF(MY_SEEK_NOT_DONE) | info->lock_wait))
goto err;
#endif
- filepos= ((share->state.dellink != HA_OFFSET_ERROR) ?
+ filepos= ((share->state.dellink != HA_OFFSET_ERROR &&
+ !info->append_insert_at_end) ?
share->state.dellink :
info->state->data_file_length);
--- 1.75/myisam/myisamdef.h Wed Apr 13 17:06:28 2005
+++ 1.76/myisam/myisamdef.h Fri May 13 12:08:02 2005
@@ -271,6 +271,7 @@
uint preload_buff_size; /* When preloading indexes */
myf lock_wait; /* is 0 or MY_DONT_WAIT */
my_bool was_locked; /* Was locked in panic */
+ my_bool append_insert_at_end; /* Set if concurrent insert */
my_bool quick_mode;
my_bool page_changed; /* If info->buff can't be used for rnext */
my_bool buff_used; /* If info->buff has to be reread for rnext */
@@ -702,7 +703,7 @@
const byte *record, my_off_t pos);
int mi_unique_comp(MI_UNIQUEDEF *def, const byte *a, const byte *b,
my_bool null_are_equal);
-void mi_get_status(void* param);
+void mi_get_status(void* param, int concurrent_insert);
void mi_update_status(void* param);
void mi_copy_status(void* to,void *from);
my_bool mi_check_status(void* param);
--- 1.40/mysys/thr_lock.c Sat Jan 15 12:28:32 2005
+++ 1.41/mysys/thr_lock.c Fri May 13 12:08:03 2005
@@ -409,7 +409,7 @@
result=0;
statistic_increment(locks_waited, &THR_LOCK_lock);
if (data->lock->get_status)
- (*data->lock->get_status)(data->status_param);
+ (*data->lock->get_status)(data->status_param, 0);
check_locks(data->lock,"got wait_for_lock",0);
}
pthread_mutex_unlock(&data->lock->mutex);
@@ -468,7 +468,7 @@
lock->read_no_write_count++;
check_locks(lock,"read lock with old write lock",0);
if (lock->get_status)
- (*lock->get_status)(data->status_param);
+ (*lock->get_status)(data->status_param, 0);
statistic_increment(locks_immediate,&THR_LOCK_lock);
goto end;
}
@@ -489,7 +489,7 @@
data->prev=lock->read.last;
lock->read.last= &data->next;
if (lock->get_status)
- (*lock->get_status)(data->status_param);
+ (*lock->get_status)(data->status_param, 0);
if ((int) lock_type == (int) TL_READ_NO_INSERT)
lock->read_no_write_count++;
check_locks(lock,"read lock with no write locks",0);
@@ -567,7 +567,7 @@
lock->write.last= &data->next;
check_locks(lock,"second write lock",0);
if (data->lock->get_status)
- (*data->lock->get_status)(data->status_param);
+ (*data->lock->get_status)(data->status_param, 0);
statistic_increment(locks_immediate,&THR_LOCK_lock);
goto end;
}
@@ -580,9 +580,16 @@
(ulong) lock->write_wait.data));
if (!lock->write_wait.data)
{ /* no scheduled write locks */
- if (lock_type == TL_WRITE_CONCURRENT_INSERT &&
- (*lock->check_status)(data->status_param))
- data->type=lock_type= thr_upgraded_concurrent_insert_lock;
+ my_bool concurrent_insert= 0;
+ if (lock_type == TL_WRITE_CONCURRENT_INSERT)
+ {
+ concurrent_insert= 1;
+ if ((*lock->check_status)(data->status_param))
+ {
+ concurrent_insert= 0;
+ data->type=lock_type= thr_upgraded_concurrent_insert_lock;
+ }
+ }
if (!lock->read.data ||
(lock_type <= TL_WRITE_DELAYED &&
@@ -594,7 +601,7 @@
data->prev=lock->write.last;
lock->write.last= &data->next;
if (data->lock->get_status)
- (*data->lock->get_status)(data->status_param);
+ (*data->lock->get_status)(data->status_param, concurrent_insert);
check_locks(lock,"only write lock",0);
statistic_increment(locks_immediate,&THR_LOCK_lock);
goto end;
@@ -1033,7 +1040,7 @@
if (!lock->read.data) /* No read locks */
{ /* We have the lock */
if (data->lock->get_status)
- (*data->lock->get_status)(data->status_param);
+ (*data->lock->get_status)(data->status_param, 0);
pthread_mutex_unlock(&lock->mutex);
DBUG_RETURN(0);
}
@@ -1221,7 +1228,8 @@
/* The following functions is for WRITE_CONCURRENT_INSERT */
-static void test_get_status(void* param __attribute__((unused)))
+static void test_get_status(void* param __attribute__((unused)),
+ int concurrent_insert __attribute__((unused)))
{
}
--- 1.465/sql/mysqld.cc Fri May 6 13:46:04 2005
+++ 1.466/sql/mysqld.cc Fri May 13 12:08:03 2005
@@ -4389,9 +4389,9 @@
(gptr*) &max_system_variables.completion_type, 0, GET_ULONG,
REQUIRED_ARG, 0, 0, 2, 0, 1, 0},
{"concurrent-insert", OPT_CONCURRENT_INSERT,
- "Use concurrent insert with MyISAM. Disable with --skip-concurrent-insert.",
+ "Use concurrent insert with MyISAM. Disable with --concurrent-insert=0",
(gptr*) &myisam_concurrent_insert, (gptr*) &myisam_concurrent_insert,
- 0, GET_BOOL, NO_ARG, 1, 0, 0, 0, 0, 0},
+ 0, GET_LONG, OPT_ARG, 1, 0, 2, 0, 0, 0},
{"console", OPT_CONSOLE, "Write error output on screen; Don't remove the console window on windows.",
(gptr*) &opt_console, (gptr*) &opt_console, 0, GET_BOOL, NO_ARG, 0, 0, 0,
0, 0, 0},
@@ -5332,9 +5332,9 @@
"Default pointer size to be used for MyISAM tables.",
(gptr*) &myisam_data_pointer_size,
(gptr*) &myisam_data_pointer_size, 0, GET_ULONG, REQUIRED_ARG,
- 4, 2, 8, 0, 1, 0},
+ 6, 2, 8, 0, 1, 0},
{"myisam_max_extra_sort_file_size", OPT_MYISAM_MAX_EXTRA_SORT_FILE_SIZE,
- "Used to help MySQL to decide when to use the slow but safe key cache index create method.",
+ "Depricated option",
(gptr*) &global_system_variables.myisam_max_extra_sort_file_size,
(gptr*) &max_system_variables.myisam_max_extra_sort_file_size,
0, GET_ULL, REQUIRED_ARG, (ulonglong) MI_MAX_TEMP_LENGTH,
@@ -5870,7 +5870,8 @@
/* Things reset to zero */
opt_skip_slave_start= opt_reckless_slave = 0;
mysql_home[0]= pidfile_name[0]= log_error_file[0]= 0;
- opt_log= opt_update_log= opt_bin_log= opt_slow_log= 0;
+ opt_log= opt_update_log= opt_slow_log= 0;
+ opt_bin_log= 1; // Enable binlog by default
opt_disable_networking= opt_skip_show_db=0;
opt_logname= opt_update_logname= opt_binlog_index_name= opt_slow_logname= 0;
opt_tc_log_file= (char *)"tc.log"; // no hostname in tc_log file name !
@@ -6170,7 +6171,7 @@
opt_update_log=1;
break;
case (int) OPT_BIN_LOG:
- opt_bin_log=1;
+ opt_bin_log= test(argument != disabled_my_option);
break;
case (int) OPT_ERROR_LOG_FILE:
opt_error_log= 1;
@@ -6570,6 +6571,13 @@
ha_open_options|=HA_OPEN_ABORT_IF_CRASHED;
break;
}
+ case OPT_CONCURRENT_INSERT:
+ /* The following code is mainly here to emulate old behavior */
+ if (!argument) /* --concurrent-insert */
+ myisam_concurrent_insert= 1;
+ else if (argument == disabled_my_option)
+ myisam_concurrent_insert= 0; /* --skip-concurrent-insert */
+ break;
case OPT_TC_HEURISTIC_RECOVER:
{
if ((tc_heuristic_recover=find_type(argument,
@@ -6747,8 +6755,6 @@
my_default_record_cache_size=global_system_variables.read_buff_size;
myisam_max_temp_length=
(my_off_t) global_system_variables.myisam_max_sort_file_size;
- myisam_max_extra_temp_length=
- (my_off_t) global_system_variables.myisam_max_extra_sort_file_size;
/* Set global variables based on startup options */
myisam_block_size=(uint) 1 << my_bit_log2(opt_myisam_block_size);
--- 1.78/mysql-test/r/view.result Fri Apr 22 23:13:42 2005
+++ 1.79/mysql-test/r/view.result Fri May 13 12:08:03 2005
@@ -1,4 +1,4 @@
-drop table if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6;
+drop table if exists t1,t2,t9,`t1a``b`,v1,v2,v3,v4,v5,v6;
drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6;
drop database if exists mysqltest;
use test;
@@ -149,7 +149,7 @@
v6 VIEW
show table status;
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t1 MyISAM 10 Fixed 5 9 45 38654705663 1024 0 NULL # # NULL latin1_swedish_ci NULL
+t1 MyISAM 10 Fixed 5 9 45 2533274790395903 1024 0 NULL # # NULL latin1_swedish_ci NULL
v1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL # # NULL NULL NULL NULL view
v2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL # # NULL NULL NULL NULL view
v3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL # # NULL NULL NULL NULL view
--- 1.67/mysql-test/t/view.test Fri Apr 22 23:13:42 2005
+++ 1.68/mysql-test/t/view.test Fri May 13 12:08:03 2005
@@ -1,5 +1,5 @@
--disable_warnings
-drop table if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6;
+drop table if exists t1,t2,t9,`t1a``b`,v1,v2,v3,v4,v5,v6;
drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6;
drop database if exists mysqltest;
--enable_warnings
--- 1.39/mysql-test/r/ps_1general.result Sat Apr 23 01:05:00 2005
+++ 1.40/mysql-test/r/ps_1general.result Fri May 13 12:08:03 2005
@@ -290,11 +290,11 @@
prepare stmt4 from ' show table status from test like ''t2%'' ';
execute stmt4;
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t2 MyISAM 10 Fixed 0 0 0 64424509439 1024 0 NULL # # # latin1_swedish_ci NULL
+t2 MyISAM 10 Fixed 0 0 0 4222124650659839 1024 0 NULL # # # latin1_swedish_ci NULL
prepare stmt4 from ' show table status from test like ''t9%'' ';
execute stmt4;
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t9 MyISAM 10 Dynamic 2 216 432 4294967295 2048 0 NULL # # # latin1_swedish_ci NULL
+t9 MyISAM 10 Dynamic 2 216 432 281474976710655 2048 0 NULL # # # latin1_swedish_ci NULL
prepare stmt4 from ' show status like ''Threads_running'' ';
execute stmt4;
Variable_name Value
@@ -803,4 +803,4 @@
execute stmt1 ;
prepare stmt1 from ' select * from t5 ' ;
execute stmt1 ;
-drop table t5 ;
+drop table t5, t9;
--- 1.22/mysql-test/t/ps_1general.test Mon Apr 4 22:43:55 2005
+++ 1.23/mysql-test/t/ps_1general.test Fri May 13 12:08:03 2005
@@ -833,7 +833,7 @@
--disable_metadata
--horizontal_results
-drop table t5 ;
+drop table t5, t9;
##### RULES OF THUMB TO PRESERVE THE SYSTEMATICS OF THE PS TEST CASES #####
#
--- 1.8/mysql-test/r/preload.result Thu Feb 24 23:33:37 2005
+++ 1.9/mysql-test/r/preload.result Fri May 13 12:08:03 2005
@@ -55,15 +55,15 @@
4181
show status like "key_read%";
Variable_name Value
-Key_read_requests 217
-Key_reads 45
+Key_read_requests 294
+Key_reads 60
select count(*) from t1 where b = 'test1';
count(*)
4181
show status like "key_read%";
Variable_name Value
-Key_read_requests 434
-Key_reads 45
+Key_read_requests 588
+Key_reads 60
flush tables;
flush status;
select @@preload_buffer_size;
@@ -74,15 +74,15 @@
test.t1 preload_keys status OK
show status like "key_read%";
Variable_name Value
-Key_read_requests 581
-Key_reads 581
+Key_read_requests 774
+Key_reads 774
select count(*) from t1 where b = 'test1';
count(*)
4181
show status like "key_read%";
Variable_name Value
-Key_read_requests 798
-Key_reads 581
+Key_read_requests 1068
+Key_reads 774
flush tables;
flush status;
show status like "key_read%";
@@ -98,15 +98,15 @@
test.t1 preload_keys status OK
show status like "key_read%";
Variable_name Value
-Key_read_requests 10
-Key_reads 10
+Key_read_requests 17
+Key_reads 17
select count(*) from t1 where b = 'test1';
count(*)
4181
show status like "key_read%";
Variable_name Value
-Key_read_requests 227
-Key_reads 52
+Key_read_requests 311
+Key_reads 75
flush tables;
flush status;
show status like "key_read%";
@@ -123,8 +123,8 @@
test.t2 preload_keys status OK
show status like "key_read%";
Variable_name Value
-Key_read_requests 587
-Key_reads 587
+Key_read_requests 785
+Key_reads 785
select count(*) from t1 where b = 'test1';
count(*)
4181
@@ -133,8 +133,8 @@
2584
show status like "key_read%";
Variable_name Value
-Key_read_requests 938
-Key_reads 613
+Key_read_requests 1266
+Key_reads 821
flush tables;
flush status;
show status like "key_read%";
@@ -149,8 +149,8 @@
Error 1146 Table 'test.t3' doesn't exist
show status like "key_read%";
Variable_name Value
-Key_read_requests 355
-Key_reads 355
+Key_read_requests 478
+Key_reads 478
flush tables;
flush status;
show status like "key_read%";
--- 1.38/mysql-test/r/merge.result Fri Dec 31 15:30:46 2004
+++ 1.39/mysql-test/r/merge.result Fri May 13 12:08:03 2005
@@ -39,7 +39,7 @@
1 SIMPLE t3 range a a 4 NULL 18 Using where
explain select * from t3 where a > 10 and a < 20;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 range a a 4 NULL 16 Using where
+1 SIMPLE t3 range a a 4 NULL 17 Using where
select * from t3 where a = 10;
a b
10 Testing
--- 1.31/mysql-test/r/null.result Wed Apr 6 06:55:03 2005
+++ 1.32/mysql-test/r/null.result Fri May 13 12:08:03 2005
@@ -170,7 +170,7 @@
insert into t1 values(null);
explain select * from t1 where i=2 or i is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null i i 5 const 10 Using where; Using index
+1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index
select count(*) from t1 where i=2 or i is null;
count(*)
10
@@ -179,7 +179,7 @@
Warning 1265 Data truncated for column 'i' at row 513
explain select * from t1 where i=2 or i is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref i i 4 const 8 Using index
+1 SIMPLE t1 ref i i 4 const 7 Using index
select count(*) from t1 where i=2 or i is null;
count(*)
9
--- 1.33/mysql-test/r/range.result Thu Apr 21 00:55:29 2005
+++ 1.34/mysql-test/r/range.result Fri May 13 12:08:03 2005
@@ -421,19 +421,19 @@
test.t2 analyze status Table is already up to date
explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range uid_index uid_index 4 NULL 128 Using where
+1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range uid_index uid_index 4 NULL 128 Using where
+1 SIMPLE t1 range uid_index uid_index 4 NULL 112 Using where
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range uid_index uid_index 4 NULL 129 Using where
+1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range uid_index uid_index 4 NULL 129 Using where
+1 SIMPLE t1 range uid_index uid_index 4 NULL 113 Using where
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
id name uid id name uid
--- 1.62/mysql-test/r/variables.result Thu May 5 23:01:35 2005
+++ 1.63/mysql-test/r/variables.result Fri May 13 12:08:03 2005
@@ -117,27 +117,23 @@
Warnings:
Note 1003 select sql_no_cache 345 AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,345 AS `@@identity`
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
-set global concurrent_insert=ON;
+set global concurrent_insert=2;
show variables like 'concurrent_insert';
Variable_name Value
-concurrent_insert ON
+concurrent_insert 2
set global concurrent_insert=1;
show variables like 'concurrent_insert';
Variable_name Value
-concurrent_insert ON
+concurrent_insert 1
set global concurrent_insert=0;
show variables like 'concurrent_insert';
Variable_name Value
-concurrent_insert OFF
-set global concurrent_insert=OFF;
-show variables like 'concurrent_insert';
-Variable_name Value
-concurrent_insert OFF
+concurrent_insert 0
set global concurrent_insert=DEFAULT;
-show variables like 'concurrent_insert';
-Variable_name Value
-concurrent_insert ON
-set global timed_mutexes=1;
+select @@concurrent_insert;
+@@concurrent_insert
+1
+set global timed_mutexes=ON;
show variables like 'timed_mutexes';
Variable_name Value
timed_mutexes ON
@@ -281,8 +277,6 @@
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable
set myisam_max_sort_file_size=100;
ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
-set myisam_max_extra_sort_file_size=100;
-ERROR HY000: Variable 'myisam_max_extra_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
set @@SQL_WARNINGS=NULL;
ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
set autocommit=1;
@@ -338,11 +332,6 @@
@@max_user_connections
100
set global max_write_lock_count=100;
-set global myisam_max_extra_sort_file_size=100;
-select @@myisam_max_extra_sort_file_size;
-@@myisam_max_extra_sort_file_size
-100
-set global myisam_max_sort_file_size=100;
set myisam_sort_buffer_size=100;
set net_buffer_length=100;
set net_read_timeout=100;
--- 1.38/mysql-test/t/variables.test Sat Apr 2 06:02:07 2005
+++ 1.39/mysql-test/t/variables.test Fri May 13 12:08:03 2005
@@ -72,23 +72,20 @@
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
-set global concurrent_insert=ON;
+set global concurrent_insert=2;
show variables like 'concurrent_insert';
set global concurrent_insert=1;
show variables like 'concurrent_insert';
set global concurrent_insert=0;
show variables like 'concurrent_insert';
-set global concurrent_insert=OFF;
-show variables like 'concurrent_insert';
set global concurrent_insert=DEFAULT;
-show variables like 'concurrent_insert';
+select @@concurrent_insert;
-set global timed_mutexes=1;
+set global timed_mutexes=ON;
show variables like 'timed_mutexes';
set global timed_mutexes=0;
show variables like 'timed_mutexes';
-
set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE";
show local variables like 'storage_engine';
show global variables like 'storage_engine';
@@ -174,8 +171,6 @@
select @@global.sql_auto_is_null;
--error 1229
set myisam_max_sort_file_size=100;
---error 1229
-set myisam_max_extra_sort_file_size=100;
--error 1231
set @@SQL_WARNINGS=NULL;
@@ -222,9 +217,6 @@
set global max_user_connections=100;
select @@max_user_connections;
set global max_write_lock_count=100;
-set global myisam_max_extra_sort_file_size=100;
-select @@myisam_max_extra_sort_file_size;
-set global myisam_max_sort_file_size=100;
set myisam_sort_buffer_size=100;
set net_buffer_length=100;
set net_read_timeout=100;
--- 1.60/mysql-test/r/myisam.result Tue Apr 26 17:46:46 2005
+++ 1.61/mysql-test/r/myisam.result Fri May 13 12:08:03 2005
@@ -1203,3 +1203,60 @@
Got one of the listed errors
drop table t1;
ERROR 42S02: Unknown table 't1'
+set @save_concurrent_insert=@@concurrent_insert;
+set global concurrent_insert=1;
+create table t1 (a int);
+insert into t1 values (1),(2),(3),(4),(5);
+lock table t1 read local;
+insert into t1 values(6),(7);
+unlock tables;
+delete from t1 where a>=3 and a<=4;
+lock table t1 read local;
+set global concurrent_insert=2;
+insert into t1 values (8),(9);
+unlock tables;
+insert into t1 values (10),(11),(12);
+select * from t1;
+a
+1
+2
+11
+10
+5
+6
+7
+8
+9
+12
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+drop table t1;
+create table t1 (a int, b varchar(30) default "hello");
+insert into t1 (a) values (1),(2),(3),(4),(5);
+lock table t1 read local;
+insert into t1 (a) values(6),(7);
+unlock tables;
+delete from t1 where a>=3 and a<=4;
+lock table t1 read local;
+set global concurrent_insert=2;
+insert into t1 (a) values (8),(9);
+unlock tables;
+insert into t1 (a) values (10),(11),(12);
+select a from t1;
+a
+1
+2
+11
+10
+5
+6
+7
+8
+9
+12
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+drop table t1;
+set global concurrent_insert=@save_concurrent_insert;
--- 1.42/mysql-test/t/myisam.test Sat Apr 16 18:58:09 2005
+++ 1.43/mysql-test/t/myisam.test Fri May 13 12:08:03 2005
@@ -606,3 +606,57 @@
drop table t1;
--error 1051
drop table t1;
+
+#
+# Test concurrent insert
+# First with static record length
+#
+set @save_concurrent_insert=@@concurrent_insert;
+set global concurrent_insert=1;
+create table t1 (a int);
+insert into t1 values (1),(2),(3),(4),(5);
+lock table t1 read local;
+connect (con1,localhost,root,,);
+connection con1;
+# Insert in table without hole
+insert into t1 values(6),(7);
+connection default;
+unlock tables;
+delete from t1 where a>=3 and a<=4;
+lock table t1 read local;
+connection con1;
+set global concurrent_insert=2;
+# Insert in table with hole -> Should insert at end
+insert into t1 values (8),(9);
+connection default;
+unlock tables;
+# Insert into hole
+insert into t1 values (10),(11),(12);
+select * from t1;
+check table t1;
+drop table t1;
+
+# Same test with dynamic record length
+create table t1 (a int, b varchar(30) default "hello");
+insert into t1 (a) values (1),(2),(3),(4),(5);
+lock table t1 read local;
+connect (con1,localhost,root,,);
+connection con1;
+# Insert in table without hole
+insert into t1 (a) values(6),(7);
+connection default;
+unlock tables;
+delete from t1 where a>=3 and a<=4;
+lock table t1 read local;
+connection con1;
+set global concurrent_insert=2;
+# Insert in table with hole -> Should insert at end
+insert into t1 (a) values (8),(9);
+connection default;
+unlock tables;
+# Insert into hole
+insert into t1 (a) values (10),(11),(12);
+select a from t1;
+check table t1;
+drop table t1;
+set global concurrent_insert=@save_concurrent_insert;
--- 1.109/sql/set_var.cc Thu May 5 18:09:13 2005
+++ 1.110/sql/set_var.cc Fri May 13 12:08:03 2005
@@ -108,7 +108,6 @@
static void fix_max_join_size(THD *thd, enum_var_type type);
static void fix_query_cache_size(THD *thd, enum_var_type type);
static void fix_query_cache_min_res_unit(THD *thd, enum_var_type type);
-static void fix_myisam_max_extra_sort_file_size(THD *thd, enum_var_type type);
static void fix_myisam_max_sort_file_size(THD *thd, enum_var_type type);
static void fix_max_binlog_size(THD *thd, enum_var_type type);
static void fix_max_relay_log_size(THD *thd, enum_var_type type);
@@ -158,8 +157,8 @@
sys_var_collation_connection sys_collation_connection("collation_connection");
sys_var_collation_database sys_collation_database("collation_database");
sys_var_collation_server sys_collation_server("collation_server");
-sys_var_bool_ptr sys_concurrent_insert("concurrent_insert",
- &myisam_concurrent_insert);
+sys_var_long_ptr sys_concurrent_insert("concurrent_insert",
+ &myisam_concurrent_insert);
sys_var_long_ptr sys_connect_timeout("connect_timeout",
&connect_timeout);
sys_var_enum sys_delay_key_write("delay_key_write",
@@ -270,7 +269,6 @@
&SV::multi_range_count);
sys_var_long_ptr sys_myisam_data_pointer_size("myisam_data_pointer_size",
&myisam_data_pointer_size);
-sys_var_thd_ulonglong sys_myisam_max_extra_sort_file_size("myisam_max_extra_sort_file_size", &SV::myisam_max_extra_sort_file_size, fix_myisam_max_extra_sort_file_size, 1);
sys_var_thd_ulonglong sys_myisam_max_sort_file_size("myisam_max_sort_file_size", &SV::myisam_max_sort_file_size, fix_myisam_max_sort_file_size, 1);
sys_var_thd_ulong sys_myisam_repair_threads("myisam_repair_threads", &SV::myisam_repair_threads);
sys_var_thd_ulong sys_myisam_sort_buffer_size("myisam_sort_buffer_size", &SV::myisam_sort_buff_size);
@@ -623,7 +621,6 @@
&sys_max_write_lock_count,
&sys_multi_range_count,
&sys_myisam_data_pointer_size,
- &sys_myisam_max_extra_sort_file_size,
&sys_myisam_max_sort_file_size,
&sys_myisam_repair_threads,
&sys_myisam_sort_buffer_size,
@@ -882,9 +879,6 @@
{sys_max_write_lock_count.name, (char*) &sys_max_write_lock_count,SHOW_SYS},
{sys_multi_range_count.name, (char*) &sys_multi_range_count, SHOW_SYS},
{sys_myisam_data_pointer_size.name, (char*) &sys_myisam_data_pointer_size, SHOW_SYS},
- {sys_myisam_max_extra_sort_file_size.name,
- (char*) &sys_myisam_max_extra_sort_file_size,
- SHOW_SYS},
{sys_myisam_max_sort_file_size.name, (char*) &sys_myisam_max_sort_file_size,
SHOW_SYS},
{"myisam_recover_options", (char*) &myisam_recover_options_str, SHOW_CHAR_PTR},
@@ -1137,14 +1131,6 @@
if (type != OPT_GLOBAL)
thd->update_lock_default= (thd->variables.low_priority_updates ?
TL_WRITE_LOW_PRIORITY : TL_WRITE);
-}
-
-
-static void
-fix_myisam_max_extra_sort_file_size(THD *thd, enum_var_type type)
-{
- myisam_max_extra_temp_length=
- (my_off_t) global_system_variables.myisam_max_extra_sort_file_size;
}
--- 1.12/mysql-test/r/gis-rtree.result Fri Apr 29 18:21:56 2005
+++ 1.13/mysql-test/r/gis-rtree.result Fri May 13 12:08:02 2005
@@ -167,7 +167,7 @@
150
EXPLAIN SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range g g 32 NULL 7 Using where
+1 SIMPLE t1 range g g 32 NULL 8 Using where
SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
fid AsText(g)
1 LINESTRING(150 150,150 150)
--- 1.13/mysql-test/r/index_merge.result Wed Feb 2 06:51:40 2005
+++ 1.14/mysql-test/r/index_merge.result Fri May 13 12:08:02 2005
@@ -17,11 +17,11 @@
test.t0 analyze status OK
explain select * from t0 where key1 < 3 or key1 > 1020;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 range i1 i1 4 NULL 55 Using where
+1 SIMPLE t0 range i1 i1 4 NULL 78 Using where
explain
select * from t0 where key1 < 3 or key2 > 1020;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 31 Using sort_union(i1,i2); Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 45 Using sort_union(i1,i2); Using where
select * from t0 where key1 < 3 or key2 > 1020;
key1 key2 key3 key4 key5 key6 key7 key8
1 1 1 1 1 1 1 1023
@@ -36,7 +36,7 @@
explain
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 9 Using sort_union(i1,i2); Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 11 Using sort_union(i1,i2); Using where
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
key1 key2 key3 key4 key5 key6 key7 key8
31 31 31 31 31 31 31 993
@@ -90,7 +90,7 @@
explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
(key3=10) or (key4 <=> null);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL 5 Using sort_union(i1,i3); Using where
+1 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL 6 Using sort_union(i1,i3); Using where
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
id select_type table type possible_keys key key_len ref rows Extra
@@ -113,7 +113,7 @@
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 100);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 range i1,i2,i3 i3 4 NULL 96 Using where
+1 SIMPLE t0 range i1,i2,i3 i3 4 NULL 95 Using where
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 1000);
id select_type table type possible_keys key key_len ref rows Extra
@@ -152,7 +152,7 @@
or
((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL 21 Using sort_union(i3,i5,i7,i8); Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL 20 Using sort_union(i3,i5,i7,i8); Using where
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
@@ -257,7 +257,7 @@
select * from t0,t1 where (t0.key1=t1.key1) and
(t0.key1=3 or t0.key2=4) and t1.key1<200;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 range i1,i2 i1 4 NULL 179 Using where
+1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where
1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
explain
select * from t0,t1 where (t0.key1=t1.key1) and
@@ -345,8 +345,8 @@
where (A.key1 < 500000 or A.key2 < 3)
and (B.key1 < 500000 or B.key2 < 3);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1016 Using sort_union(i1,i2); Using where
-1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1016 Using sort_union(i1,i2); Using where
+1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where
+1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
where (A.key1 < 500000 or A.key2 < 3)
@@ -371,11 +371,11 @@
update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
from t0 as A, t0 as B
-where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7or16 = 1 or A.key8=1)
-and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7or16 = 1 or B.key8=1);
+where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key# = 1 or A.key8=1)
+and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key# = 1 or B.key8=1);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL 7or16 Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
-1 SIMPLE B index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL 7or16 Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
+1 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
+1 SIMPLE B index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
from t0 as A, t0 as B
where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
--- 1.5/mysql-test/r/index_merge_ror.result Thu Apr 21 12:32:43 2005
+++ 1.6/mysql-test/r/index_merge_ror.result Fri May 13 12:08:03 2005
@@ -4,13 +4,13 @@
64801
explain select key1,key2 from t1 where key1=100 and key2=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where; Using index
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 58 Using intersect(key1,key2); Using where; Using index
select key1,key2 from t1 where key1=100 and key2=100;
key1 key2
100 100
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1 key2 key3 key4 filler1
100 100 100 100 key1-key2-key3-key4
@@ -18,21 +18,21 @@
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 58 Using intersect(key1,key2); Using where
select key1,key2,filler1 from t1 where key1=100 and key2=100;
key1 key2 filler1
100 100 key1-key2-key3-key4
100 100 key1-key2
explain select key1,key2 from t1 where key1=100 and key2=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where; Using index
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 58 Using intersect(key1,key2); Using where; Using index
select key1,key2 from t1 where key1=100 and key2=100;
key1 key2
100 100
100 100
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1 key2 key3 key4
100 100 100 100
@@ -40,7 +40,7 @@
-1 -1 100 100
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1 key2 key3 key4 filler1
100 100 100 100 key1-key2-key3-key4
@@ -48,14 +48,14 @@
-1 -1 100 100 key4-key3
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 1 Using intersect(key1,key2,key3); Using where; Using index
+1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 2 Using intersect(key1,key2,key3); Using where; Using index
select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
key1 key2 key3
100 100 100
insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 5 Using union(intersect(key1,key2),key3); Using where
+1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 83 Using union(intersect(key1,key2),key3); Using where
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
key1 key2 key3 key4 filler1
100 100 100 100 key1-key2-key3-key4
@@ -72,19 +72,19 @@
key1 key2 filler1
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 152 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1 key2 key3 key4 filler1
-1 -1 100 100 key4-key3
delete from t1 where key3=100 and key4=100;
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 152 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1 key2 key3 key4 filler1
explain select key1,key2 from t1 where key1=100 and key2=100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where; Using index
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 57 Using intersect(key1,key2); Using where; Using index
select key1,key2 from t1 where key1=100 and key2=100;
key1 key2
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
@@ -92,7 +92,7 @@
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 16 Using union(key3,intersect(key1,key2),key4); Using where
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 136 Using union(key3,intersect(key1,key2),key4); Using where
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
key1 key2 key3 key4 filler1
100 100 200 200 key1-key2-key3-key4-3
@@ -101,7 +101,7 @@
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 18 Using union(key3,intersect(key1,key2),key4); Using where
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 146 Using union(key3,intersect(key1,key2),key4); Using where
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
key1 key2 key3 key4 filler1
100 100 200 200 key1-key2-key3-key4-3
@@ -111,7 +111,7 @@
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 20 Using union(key3,intersect(key1,key2),key4); Using where
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 156 Using union(key3,intersect(key1,key2),key4); Using where
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
key1 key2 key3 key4 filler1
100 100 200 200 key1-key2-key3-key4-3
@@ -121,50 +121,50 @@
-1 -1 200 -1 key3
explain select * from t1 where st_a=1 and st_b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 2508 Using intersect(st_a,st_b); Using where
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 2637 Using intersect(st_a,st_b); Using where
explain select st_a,st_b from t1 where st_a=1 and st_b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 2508 Using intersect(st_a,st_b); Using where; Using index
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 2637 Using intersect(st_a,st_b); Using where; Using index
explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 14720 Using where
+1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 15093 Using where
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt12a 12 const,const,const 958
+1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 971
explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1b 8 const,const 3757 Using where
+1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const 3879 Using where
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 42 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 44 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt12a,stb_swt1b 12,8 NULL 42 Using intersect(sta_swt12a,stb_swt1b); Using where
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt12a,stb_swt1b 12,8 NULL 44 Using intersect(sta_swt12a,stb_swt1b); Using where
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt1a,sta_swt2a,stb_swt1b 8,8,8 NULL 41 Using intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where
+1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt1a,sta_swt2a,stb_swt1b 8,8,8 NULL 43 Using intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL 159 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where
+1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL 168 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where
explain select * from t1
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 42 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 44 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
explain select * from t1
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 163 Using intersect(sta_swt1a,stb_swt1b); Using where
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 174 Using intersect(sta_swt1a,stb_swt1b); Using where
explain select st_a from t1
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 163 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 174 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
explain select st_a from t1
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 163 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 174 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
drop table t0,t1;
create table t2 (
a char(10),
--- 1.8/mysql-test/t/index_merge.test Wed Feb 2 06:51:40 2005
+++ 1.9/mysql-test/t/index_merge.test Fri May 13 12:08:03 2005
@@ -310,7 +310,7 @@
# The next query will not use index i7 in intersection if the OS doesn't
# support file sizes > 2GB. (ha_myisam::ref_length depends on this and index
# scan cost estimates depend on ha_myisam::ref_length)
---replace_result "4,4,4,4,4,4,4" X "4,4,4,4,4,4" X "i6,i7" "i6,i7?" "i6" "i6,i7?" 7 7or16 16 7or16
+--replace_result "4,4,4,4,4,4,4" X "4,4,4,4,4,4" X "i6,i7" "i6,i7?" "i6" "i6,i7?" 7 # 16 # 18 #
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
from t0 as A, t0 as B
where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
--- 1.8/mysql-test/r/group_min_max.result Mon Feb 28 16:19:57 2005
+++ 1.9/mysql-test/r/group_min_max.result Fri May 13 12:08:02 2005
@@ -287,7 +287,7 @@
b m422 p422
explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by
+1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
@@ -317,22 +317,22 @@
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 13 Using where; Using index for group-by
+1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 12 Using where; Using index for group-by
+1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 14 Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where a1 < 'd' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 12 Using where; Using index for group-by
+1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 14 Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'b' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 16 Using where; Using index for group-by
explain select a1,a2,b, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 18 Using where; Using index for group-by
+1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 17 Using where; Using index for group-by
explain select a1, max(c) from t2 where a1 >= 'c' or a1 < 'b' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 18 Using where; Using index for group-by
+1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 17 Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where a1 >= 'c' or a2 < 'b' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 21 Using where; Using index for group-by
@@ -353,7 +353,7 @@
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 16 Using where; Using index for group-by
explain select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 13 Using where; Using index for group-by
+1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 14 Using where; Using index for group-by
select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
a1 a2 b min(c) max(c)
a a a a111 d111
@@ -1398,10 +1398,10 @@
1 SIMPLE t2 range NULL idx_t2_1 163 NULL 21 Using where; Using index for group-by
explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 17 Using where; Using index for group-by
+1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 16 Using where; Using index for group-by
explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 17 Using where; Using index for group-by
+1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 16 Using where; Using index for group-by
explain select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 16 Using where; Using index for group-by
@@ -1824,16 +1824,16 @@
104
explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by
+1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by
+1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by
+1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by
+1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 10 Using where; Using index for group-by
explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range NULL idx_t1_1 130 NULL 9 Using index for group-by
| Thread |
|---|
| • bk commit into 5.0 tree (monty:1.1933) | monty | 13 May |
