Below is the list of changes that have just been committed into a local
5.2 repository of psergey. When psergey 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.2155 06/05/22 19:02:45 sergefp@stripped +28 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb"
mysql-test/t/myisam_mrr.test
1.1 06/05/22 19:02:33 sergefp@stripped +14 -0
mysql-test/t/innodb_mrr.test
1.1 06/05/22 19:02:33 sergefp@stripped +49 -0
mysql-test/t/myisam_mrr.test
1.0 06/05/22 19:02:33 sergefp@stripped +0 -0
BitKeeper file /home/psergey/mysql-5.2-mymrr-03/mysql-test/t/myisam_mrr.test
mysql-test/t/innodb_mrr.test
1.0 06/05/22 19:02:33 sergefp@stripped +0 -0
BitKeeper file /home/psergey/mysql-5.2-mymrr-03/mysql-test/t/innodb_mrr.test
mysql-test/r/myisam_mrr.result
1.1 06/05/22 19:02:32 sergefp@stripped +279 -0
mysql-test/r/innodb_mrr.result
1.1 06/05/22 19:02:32 sergefp@stripped +309 -0
mysql-test/r/myisam_mrr.result
1.0 06/05/22 19:02:32 sergefp@stripped +0 -0
BitKeeper file /home/psergey/mysql-5.2-mymrr-03/mysql-test/r/myisam_mrr.result
mysql-test/r/innodb_mrr.result
1.0 06/05/22 19:02:32 sergefp@stripped +0 -0
BitKeeper file /home/psergey/mysql-5.2-mymrr-03/mysql-test/r/innodb_mrr.result
mysql-test/include/mrr_tests.inc
1.1 06/05/22 19:02:31 sergefp@stripped +106 -0
storage/myisam/mi_rkey.c
1.22 06/05/22 19:02:31 sergefp@stripped +16 -2
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Make MyISAM to be able to use "restore scan" calls:
index_read({key_tuple, rowid})
storage/myisam/mi_key.c
1.47 06/05/22 19:02:31 sergefp@stripped +27 -3
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Make MyISAM to be able to use "restore scan" calls:
index_read({key_tuple, rowid})
sql/table.h
1.135 06/05/22 19:02:31 sergefp@stripped +3 -1
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Added comments
sql/structs.h
1.59 06/05/22 19:02:31 sergefp@stripped +3 -1
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Added comments
sql/sql_select.cc
1.393 06/05/22 19:02:31 sergefp@stripped +36 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Make EXPLAIN show "Using MRR" if native MRR implementation is used
- Add comments
sql/sql_class.h
1.288 06/05/22 19:02:31 sergefp@stripped +7 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Add @@optimizer_use_mrr system variable
sql/set_var.cc
1.179 06/05/22 19:02:31 sergefp@stripped +14 -1
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Add @@optimizer_use_mrr system variable
sql/opt_range.h
1.62 06/05/22 19:02:31 sergefp@stripped +2 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Update range optimizer and QUICK_RANGE_SELECT to use new MRR interface functions
sql/opt_range.cc
1.207 06/05/22 19:02:31 sergefp@stripped +83 -18
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Update range optimizer and QUICK_RANGE_SELECT to use new MRR interface functions
- Added comments
sql/mysqld.cc
1.543 06/05/22 19:02:31 sergefp@stripped +2 -2
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Change minimum value of @@read_rnd_buff_size
sql/mysql_priv.h
1.386 06/05/22 19:02:31 sergefp@stripped +1 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Add key_zero_nulls() utility function.
sql/key.cc
1.40 06/05/22 19:02:31 sergefp@stripped +24 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Add key_zero_nulls() utility function.
sql/handler.h
1.200 06/05/22 19:02:31 sergefp@stripped +77 -3
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Add DS-MRR implementation functions: cost estimates and execution engine.
- Add needed functions into class COST_VECT
sql/handler.cc
1.222 06/05/22 19:02:31 sergefp@stripped +563 -11
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Add DS-MRR implementation functions: cost estimates and execution engine.
sql/ha_ndbcluster.h
1.125 06/05/22 19:02:31 sergefp@stripped +6 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Update the NDB table handler to use new interface (to make "Using MRR" show up in EXPLAIN)
mysql-test/include/mrr_tests.inc
1.0 06/05/22 19:02:31 sergefp@stripped +0 -0
BitKeeper file /home/psergey/mysql-5.2-mymrr-03/mysql-test/include/mrr_tests.inc
sql/ha_ndbcluster.cc
1.285 06/05/22 19:02:30 sergefp@stripped +36 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb":
- Update the NDB table handler to use new interface (to make "Using MRR" show up in EXPLAIN)
sql/ha_myisam.h
1.73 06/05/22 19:02:30 sergefp@stripped +15 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb":
Make MyISAM handler use DS-MRR implementation
sql/ha_myisam.cc
1.175 06/05/22 19:02:30 sergefp@stripped +43 -2
WL#2475 "Batched range read functions for MyISAM/InnoDb":
Make MyISAM handler use DS-MRR implementation
sql/ha_innodb.h
1.118 06/05/22 19:02:30 sergefp@stripped +14 -0
WL#2475 "Batched range read functions for MyISAM/InnoDb":
Make InnoDB handler use DS-MRR implementation
sql/ha_innodb.cc
1.262 06/05/22 19:02:30 sergefp@stripped +42 -2
WL#2475 "Batched range read functions for MyISAM/InnoDb":
Make InnoDB handler use DS-MRR implementation
mysql-test/r/ndb_condition_pushdown.result
1.18 06/05/22 19:02:30 sergefp@stripped +11 -11
WL#2475 "Batched range read functions for MyISAM/InnoDb":
updated test results
mysql-test/r/ndb_blob.result
1.18 06/05/22 19:02:30 sergefp@stripped +2 -2
WL#2475 "Batched range read functions for MyISAM/InnoDb":
updated test results
include/myisam.h
1.77 06/05/22 19:02:30 sergefp@stripped +1 -1
Fixed comment
# 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: sergefp
# Host: newbox.mylan
# Root: /home/psergey/mysql-5.2-mymrr-03
--- 1.76/include/myisam.h 2006-02-25 00:20:42 +03:00
+++ 1.77/include/myisam.h 2006-05-22 19:02:30 +04:00
@@ -129,7 +129,7 @@
(_to_)= (mi_get_mask_all_keys_active(_maxkeys_) & \
(_from_))
- /* Param to/from mi_info */
+ /* Param to/from mi_status */
typedef struct st_mi_isaminfo /* Struct from h_info */
{
--- 1.46/storage/myisam/mi_key.c 2005-11-18 18:06:35 +03:00
+++ 1.47/storage/myisam/mi_key.c 2006-05-22 19:02:31 +04:00
@@ -225,16 +225,40 @@
my_bool is_ft= info->s->keyinfo[keynr].flag & HA_FULLTEXT;
DBUG_ENTER("_mi_pack_key");
- for (keyseg=info->s->keyinfo[keynr].seg ;
- keyseg->type && (int) k_length > 0;
+ for (keyseg=info->s->keyinfo[keynr].seg; (int) k_length > 0;
old+=keyseg->length, keyseg++)
{
enum ha_base_keytype type=(enum ha_base_keytype) keyseg->type;
uint length=min((uint) keyseg->length,(uint) k_length);
uint char_length;
uchar *pos;
- CHARSET_INFO *cs=keyseg->charset;
+ CHARSET_INFO *cs;
+
+ if (!type)
+ {
+ /* Decode the ROWID value back to to file pointer */
+ if (k_length == length)
+ {
+ pos=old;
+ if (!(info->s->options & (HA_OPTION_PACK_RECORD | HA_OPTION_COMPRESS_RECORD)))
+ {
+ my_off_t rowid;
+ rowid= my_get_ptr(pos, length);
+ rowid= rowid / info->s->base.pack_reclength;
+ my_store_ptr(key, length, rowid);
+ }
+ else
+ memcpy(key, pos, length);
+ pos+=length;
+ k_length-=length;
+ key+= length;
+ keyseg++;
+ }
+ break;
+ }
+
+ cs=keyseg->charset;
if (keyseg->null_bit)
{
k_length--;
--- 1.21/storage/myisam/mi_rkey.c 2005-11-04 23:09:58 +03:00
+++ 1.22/storage/myisam/mi_rkey.c 2006-05-22 19:02:31 +04:00
@@ -30,6 +30,8 @@
MI_KEYDEF *keyinfo;
HA_KEYSEG *last_used_keyseg;
uint pack_key_length, use_key_length, nextflag;
+ uint myisam_search_flag;
+ my_bool key_tuple_has_rowid= FALSE;
DBUG_ENTER("mi_rkey");
DBUG_PRINT("enter", ("base: %lx buf: %lx inx: %d search_flag: %d",
(long) info, (long) buf, inx, search_flag));
@@ -60,9 +62,15 @@
key_buff=info->lastkey+info->s->base.max_key_length;
pack_key_length=_mi_pack_key(info,(uint) inx, key_buff, (uchar*) key,
key_len, &last_used_keyseg);
+ if (last_used_keyseg > (info->s->keyinfo[inx].seg +
+ info->s->keyinfo[inx].keysegs))
+ {
+ key_tuple_has_rowid= TRUE;
+ last_used_keyseg--;
+ }
/* Save packed_key_length for use by the MERGE engine. */
info->pack_key_length= pack_key_length;
- DBUG_EXECUTE("key",_mi_print_key(DBUG_FILE, keyinfo->seg,
+ DBUG_EXECUTE("info",_mi_print_key(DBUG_FILE, keyinfo->seg,
key_buff, pack_key_length););
}
@@ -89,8 +97,14 @@
#endif
case HA_KEY_ALG_BTREE:
default:
+ myisam_search_flag= myisam_read_vec[search_flag];
+ if (key_tuple_has_rowid)
+ {
+ /* Fiddle with flags so ha_key_cmp compares the rowid, too */
+ myisam_search_flag &= ~(SEARCH_FIND | SEARCH_NO_FIND);
+ }
if (!_mi_search(info, keyinfo, key_buff, use_key_length,
- myisam_read_vec[search_flag], info->s->state.key_root[inx]))
+ myisam_search_flag, info->s->state.key_root[inx]))
{
while (info->lastpos >= info->state->data_file_length)
{
--- 1.174/sql/ha_myisam.cc 2006-02-26 00:20:52 +03:00
+++ 1.175/sql/ha_myisam.cc 2006-05-22 19:02:30 +04:00
@@ -187,8 +187,11 @@
int_table_flags(HA_NULL_IN_KEY | HA_CAN_FULLTEXT | HA_CAN_SQL_HANDLER |
HA_DUPP_POS | HA_CAN_INDEX_BLOBS | HA_AUTO_PART_KEY |
HA_FILE_BASED | HA_CAN_GEOMETRY | HA_READ_RND_SAME |
- HA_CAN_INSERT_DELAYED | HA_CAN_BIT_FIELD),
- can_enable_indexes(1)
+ HA_CAN_INSERT_DELAYED | HA_CAN_BIT_FIELD |
+ HA_NEED_READ_RANGE_BUFFER
+ ),
+ can_enable_indexes(1),
+ ds_mrr(this)
{}
@@ -1786,3 +1789,41 @@
return COMPATIBLE_DATA_NO;
return COMPATIBLE_DATA_YES;
}
+
+
+/****************************************************************************
+ * DS-MRR implementation
+ ***************************************************************************/
+
+/**
+ * Multi Range Read interface, DS-MRR calls
+ */
+
+int ha_myisam::multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param,
+ uint n_ranges, uint mode,
+ HANDLER_BUFFER *buf)
+{
+ return ds_mrr.dsmrr_init(this, &table->key_info[active_index],
+ seq, seq_init_param, n_ranges, mode, buf);
+}
+
+int ha_myisam::multi_range_read_next(char **range_info)
+{
+ return ds_mrr.dsmrr_next(this, range_info);
+}
+
+ha_rows ha_myisam::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
+ void *seq_init_param,
+ uint n_ranges, uint *bufsz,
+ uint *flags, COST_VECT *cost)
+{
+ return ds_mrr.dsmrr_info_const(keyno, seq, seq_init_param, n_ranges, bufsz,
+ flags, cost);
+}
+
+int ha_myisam::multi_range_read_info(uint keyno, uint n_ranges, uint keys,
+ uint *bufsz, uint *flags, COST_VECT *cost)
+{
+ return ds_mrr.dsmrr_info(keyno, n_ranges, keys, bufsz, flags, cost);
+}
+
--- 1.72/sql/ha_myisam.h 2006-01-20 00:40:51 +03:00
+++ 1.73/sql/ha_myisam.h 2006-05-22 19:02:30 +04:00
@@ -132,4 +132,19 @@
int dump(THD* thd, int fd);
int net_read_dump(NET* net);
#endif
+public:
+ /**
+ * Multi Range Read interface
+ */
+ int multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param,
+ uint n_ranges, uint mode, HANDLER_BUFFER *buf);
+ int multi_range_read_next(char **range_info);
+ ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
+ void *seq_init_param,
+ uint n_ranges, uint *bufsz,
+ uint *flags, COST_VECT *cost);
+ int multi_range_read_info(uint keyno, uint n_ranges, uint keys,
+ uint *bufsz, uint *flags, COST_VECT *cost);
+private:
+ DsMrr_impl ds_mrr;
};
--- 1.221/sql/handler.cc 2006-03-18 14:44:55 +03:00
+++ 1.222/sql/handler.cc 2006-05-22 19:02:31 +04:00
@@ -2775,6 +2775,9 @@
return read_time;
}
+/****************************************************************************
+ * Default MRR implementation (MRR to non-MRR converter)
+ ***************************************************************************/
/*
Get cost and other information about MRR scan over a known list of ranges
@@ -2810,9 +2813,10 @@
Therefore, we have this function.
RETURN
- 0 - OK, *cost contains cost of the scan, *bufsz and *flags contain scan
- parameters.
- 1 - Error or can't perform the requested scan
+ HA_POS_ERROR - Error or can't perform the requested scan. Values of OUT
+ parameters are undefined.
+ other - OK, *cost contains cost of the scan, *bufsz and *flags
+ contain scan parameters.
*/
ha_rows
@@ -2852,15 +2856,17 @@
if (part > max_used_part)
max_used_part= part;
}
-
+
+ /* The following code is a copy of multi_range_read_info(): */
+ *flags |= HA_MRR_USE_DEFAULT_IMPL;
cost->zero();
+ cost->avg_io_cost= 1; // Assume random seeks
if ((*flags & HA_MRR_INDEX_ONLY) && total_rows > 2)
cost->io_count= index_only_read_time(keyno, total_rows);
else
cost->io_count= read_time(keyno, n_ranges, total_rows);
cost->cpu_cost= (double) total_rows / TIME_FOR_COMPARE + 0.01;
- cost->avg_io_cost= 1; // Assume random seeks
return total_rows;
}
@@ -2896,21 +2902,22 @@
other - Error or can't perform the requested scan
*/
-int handler::multi_range_read_info(uint keyno, uint n_ranges, uint keys,
+int handler::multi_range_read_info(uint keyno, uint n_ranges, uint n_rows,
uint *bufsz, uint *flags, COST_VECT *cost)
{
*bufsz= 0; // No need for buffer
+ *flags |= HA_MRR_USE_DEFAULT_IMPL;
+
cost->zero();
cost->avg_io_cost= 1; // Assume random seeks
- //TODO: flags & HA_MRR_SINGLEPOINT
-
+ // TODO: Special handling for (flags & HA_MRR_SINGLEPOINT) case
/* Produce the same cost as non-MRR code does */
if (*flags & HA_MRR_INDEX_ONLY)
- cost->io_count= index_only_read_time(keyno, keys);
+ cost->io_count= index_only_read_time(keyno, n_rows);
else
- cost->io_count= read_time(keyno, n_ranges, keys);
+ cost->io_count= read_time(keyno, n_ranges, n_rows);
return 0;
}
@@ -3012,7 +3019,19 @@
/* Save a call if there can be only one row in range. */
if (mrr_cur_range.range_flag != (UNIQUE_RANGE | EQ_RANGE))
{
- result= read_range_next();
+ if (mrr_restore_scan)
+ {
+ result= mrr_restore_scan_res;
+ if (!result)
+ result= (compare_key(end_range) <= 0 ? 0 : HA_ERR_END_OF_FILE);
+ mrr_restore_scan= FALSE;
+ if (!result)
+ eq_range= FALSE;
+
+ }
+ else
+ result= read_range_next();
+
/* On success or non-EOF errors jump to the end. */
if (result != HA_ERR_END_OF_FILE)
break;
@@ -3046,6 +3065,539 @@
DBUG_PRINT("exit",("handler::multi_range_read_next result %d", result));
DBUG_RETURN(result);
}
+
+
+/****************************************************************************
+ * DS-MRR implementation
+ ***************************************************************************/
+
+/*
+ DS-MRR: Initialize and start MRR scan
+
+ SYNOPSIS
+ DsMrr_impl::dsmrr_init()
+ h Table handler to be used
+ key Index to be used
+ seq_funcs Interval sequence enumeration functions
+ seq_init_param Interval sequence enumeration parameter
+ n_ranges Number of ranges in the sequence.
+ mode HA_MRR_* modes to use
+ buf INOUT Buffer to use
+
+ DESCRIPTION
+ Initialize and start the MRR scan. Depending on the mode parameter, this
+ may use default or DS-MRR implementation.
+
+ RETURN
+ 0 - Ok, Scan started.
+ other - Error
+*/
+
+int DsMrr_impl::dsmrr_init(handler *h, KEY *key,
+ RANGE_SEQ_IF *seq_funcs, void *seq_init_param,
+ uint n_ranges, uint mode, HANDLER_BUFFER *buf)
+{
+ int res;
+ uint elem_size;
+
+ if (mode & HA_MRR_USE_DEFAULT_IMPL)
+ {
+ use_default_impl= TRUE;
+ return h->handler::multi_range_read_init(seq_funcs, seq_init_param,
+ n_ranges, mode, buf);
+ }
+ use_default_impl= FALSE;
+
+ rowids_buf= (byte*)buf->buffer;
+ is_mrr_assoc= !test(mode & HA_MRR_NO_ASSOCIATION);
+ rowids_buf_end= (byte*)buf->buffer_end;
+
+ elem_size= h->ref_length + (int)is_mrr_assoc * sizeof(void*);
+ rowids_buf_last= rowids_buf +
+ ((rowids_buf_end - rowids_buf)/ elem_size)*
+ elem_size;
+ rowids_buf_end= rowids_buf_last;
+ res= h->handler::multi_range_read_init(seq_funcs, seq_init_param, n_ranges,
+ mode, buf);
+ DBUG_ASSERT(!res);
+
+ mrr_key= key;
+ mrr_keyno= h->active_index;
+
+ if (h->index_end() || h->extra(HA_EXTRA_KEYREAD) ||
+ h->extra(HA_EXTRA_RETRIEVE_PRIMARY_KEY) ||
+ h->extra(HA_EXTRA_RETRIEVE_ALL_COLS) ||
+ h->index_init(mrr_keyno, FALSE))
+ return 1;
+
+ if (!(last_idx_tuple= sql_alloc(key->key_length + h->ref_length)))
+ return 1;
+
+ h->mrr_restore_scan= FALSE;
+ /* h->mrr_restore_scan_res needs no initialization */
+
+ if ((res= dsmrr_fill_buffer(h)))
+ return res;
+
+ if (h->index_end() || h->rnd_init(FALSE))
+ return 1;
+
+ /*
+ If the above call has scanned through all intervals in *seq, then
+ adjust *buf to indicate that the remaining buffer space will not be used.
+ */
+ if (dsmrr_eof)
+ buf->end_of_used_area= rowids_buf_last;
+ return 0;
+}
+
+
+static int rowid_cmp(void *h, byte *a, byte *b)
+{
+ return ((handler*)h)->cmp_ref(a, b);
+}
+
+
+/*
+ DS-MRR: Fill the buffer with rowids and sort it by rowid
+
+ SYNOPSIS
+ DsMrr_impl::dsmrr_fill_buffer()
+ h Table handler
+
+ DESCRIPTION
+ {This is an internal function of DiskSweep MRR implementation}
+ Scan the MRR ranges and collect ROWIDs (or {ROWID, range_id} pairs) into
+ buffer. When the buffer is full or scan is completed, sort the buffer by
+ rowid and return.
+
+ The function assumes that rowids buffer is empty when it is invoked.
+
+ RETURN
+ 0 - Some rowids are in the rowids buffer, properly ordered
+ other - Error
+*/
+
+int DsMrr_impl::dsmrr_fill_buffer(handler *h)
+{
+ char *range_info;
+ int res;
+
+ rowids_buf_cur= rowids_buf;
+ while ((rowids_buf_cur < rowids_buf_end) &&
+ !(res= h->handler::multi_range_read_next(&range_info)))
+ {
+ /* Put rowid, or {rowid, range_id} pair into the buffer */
+ h->position((const byte*)(h->table->record[0]));
+ memcpy(rowids_buf_cur, h->ref, h->ref_length);
+ rowids_buf_cur += h->ref_length;
+
+ if (is_mrr_assoc)
+ {
+ memcpy(rowids_buf_cur, &range_info, sizeof(void*));
+ rowids_buf_cur += sizeof(void*);
+ }
+ }
+
+ if (res && res != HA_ERR_END_OF_FILE)
+ return res;
+ dsmrr_eof= test(res == HA_ERR_END_OF_FILE);
+
+ if (!res && (h->mrr_cur_range.range_flag != (UNIQUE_RANGE | EQ_RANGE)))
+ {
+ /* Save the index position: search tuple + rowid */
+ key_copy(last_idx_tuple, h->table->record[0], mrr_key,
+ mrr_key->key_length);
+ key_zero_nulls(last_idx_tuple, mrr_key);
+
+ memcpy(last_idx_tuple + mrr_key->key_length, h->ref, h->ref_length);
+ h->mrr_restore_scan= TRUE;
+ }
+
+ /* Sort the buffer contents by rowid */
+ uint elem_size= h->ref_length + (int)is_mrr_assoc * sizeof(void*);
+ uint n_rowids= (rowids_buf_cur - rowids_buf) / elem_size;
+
+ qsort2(rowids_buf, n_rowids, elem_size, (qsort2_cmp)rowid_cmp,
+ (void*)h);
+ rowids_buf_last= rowids_buf_cur;
+ rowids_buf_cur= rowids_buf;
+ return 0;
+}
+
+
+/*
+ DS-MRR implementation: multi_range_read_next() function
+*/
+
+int DsMrr_impl::dsmrr_next(handler *h, char **range_info)
+{
+ int res;
+
+ if (use_default_impl)
+ return h->handler::multi_range_read_next(range_info);
+
+ if ((rowids_buf_cur == rowids_buf_last))
+ {
+ fprintf(stderr, "****REFILL****\n");
+ h->rnd_end();
+ if (h->extra(HA_EXTRA_KEYREAD) ||
+ h->extra(HA_EXTRA_RETRIEVE_PRIMARY_KEY) ||
+ h->extra(HA_EXTRA_RETRIEVE_ALL_COLS) ||
+ h->index_init(mrr_keyno, FALSE))
+ return HA_ERR_KEY_NOT_FOUND;
+ /*
+ The following check is made here after index_init call so that we
+ leave the table handler in "scanning the index" state after returning
+ EOF. De-initialization code depends on this.
+ */
+ if (dsmrr_eof)
+ return HA_ERR_END_OF_FILE;
+
+ if (h->mrr_restore_scan)
+ {
+ fprintf(stderr, "** SCAN RESTORE **\n");
+ h->mrr_restore_scan_res=
+ h->index_read(h->table->record[0], last_idx_tuple,
+ mrr_key->key_length + h->ref_length,
+ HA_READ_AFTER_KEY);
+ }
+ res= dsmrr_fill_buffer(h);
+
+ h->index_end();
+ h->rnd_init(FALSE);
+
+ if (res)
+ return res;
+ }
+
+ /* Return EOF if there are no rowids in the buffer after re-fill attempt */
+ if (rowids_buf_cur == rowids_buf_last)
+ return HA_ERR_END_OF_FILE;
+
+ res= h->rnd_pos((byte*)h->table->record[0], rowids_buf_cur);
+ rowids_buf_cur += h->ref_length;
+
+ if (is_mrr_assoc)
+ {
+ memcpy(range_info, rowids_buf_cur, sizeof(void*));
+ rowids_buf_cur += sizeof(void*);
+ }
+ return res;
+}
+
+
+/*
+ DS-MRR implementation: multi_range_read_info() function
+*/
+int DsMrr_impl::dsmrr_info(uint keyno, uint n_ranges, uint rows, uint *bufsz,
+ uint *flags, COST_VECT *cost)
+{
+ int res;
+ uint def_flags= *flags;
+ uint def_bufsz= *bufsz;
+
+ /* Get cost/flags/mem_usage of default MRR implementation */
+ res= h->handler::multi_range_read_info(keyno, n_ranges, rows, &def_bufsz,
+ &def_flags, cost);
+ DBUG_ASSERT(!res);
+
+ if ((*flags & HA_MRR_USE_DEFAULT_IMPL) ||
+ choose_mrr_impl(keyno, rows, &def_flags, &def_bufsz, cost))
+ {
+ /* Default implementation is choosen */
+ DBUG_PRINT("info", ("Default MRR implementation choosen"));
+ *flags= def_flags;
+ *bufsz= def_bufsz;
+ }
+ else
+ {
+ DBUG_PRINT("info", ("DS-MRR implementation choosen"));
+ }
+
+ return 0;
+}
+
+
+/*
+ DS-MRR Implementation: multi_range_read_info_const() function
+*/
+
+ha_rows DsMrr_impl::dsmrr_info_const(uint keyno, RANGE_SEQ_IF *seq,
+ void *seq_init_param, uint n_ranges,
+ uint *bufsz, uint *flags, COST_VECT *cost)
+{
+ ha_rows rows;
+ uint def_flags= *flags;
+ uint def_bufsz= *bufsz;
+ /* Get cost/flags/mem_usage of default MRR implementation */
+ rows= h->handler::multi_range_read_info_const(keyno, seq, seq_init_param,
+ n_ranges, &def_bufsz,
+ &def_flags, cost);
+ if (rows == HA_POS_ERROR)
+ {
+ /* Default implementation can't perform MRR scan => we can't either */
+ return rows;
+ }
+
+ /*
+ If HA_MRR_USE_DEFAULT_IMPL has been passed to us, that is an order to
+ use the default MRR implementation (we need it for UPDATE/DELETE).
+ Otherwise, make a choice based on cost and @@optimizer_use_mrr.
+ */
+ if ((*flags & HA_MRR_USE_DEFAULT_IMPL) ||
+ choose_mrr_impl(keyno, rows, flags, bufsz, cost))
+ {
+ DBUG_PRINT("info", ("Default MRR implementation choosen"));
+ *flags= def_flags;
+ *bufsz= def_bufsz;
+ }
+ else
+ {
+ *flags &= ~HA_MRR_USE_DEFAULT_IMPL;
+ DBUG_PRINT("info", ("DS-MRR implementation choosen"));
+ }
+ return rows;
+}
+
+
+/*
+ DS-MRR Internals: Choose between Default MRR implementation and DS-MRR
+
+ SYNOPSIS
+ choose_mrr_impl()
+ keyno Index number
+ rows E(full rows to be retrieved)
+ flags IN MRR flags provided by the MRR user
+ OUT If DS-MRR is choosen, flags of DS-MRR implementation
+ else the value is not modified
+ bufsz IN If DS-MRR is choosen, buffer use of DS-MRR implementation
+ else the value is not modified
+ cost IN Cost of default MRR implementation
+ OUT If DS-MRR is choosen, cost of DS-MRR scan
+ else the value is not modified
+
+ DESCRIPTION
+ Make the choice between using Default MRR implementation and DS-MRR.
+ This function contains common functionality factored out of dsmrr_info()
+ and dsmrr_info_const(). The function assumes that the default MRR
+ implementation's applicability requirements are satisfied.
+
+ RETURN
+ TRUE Default MRR implementation should be used
+ FALSE DS-MRR implementation should be used
+*/
+
+bool DsMrr_impl::choose_mrr_impl(uint keyno, ha_rows rows, uint *flags,
+ uint *bufsz, COST_VECT *cost)
+{
+ COST_VECT dsmrr_cost;
+ bool res;
+ THD *thd= current_thd;
+ if ((thd->variables.optimizer_use_mrr == 2) ||
+ (*flags & HA_MRR_INDEX_ONLY) || (*flags & HA_MRR_SORTED) ||
+ (keyno == h->table_share->primary_key &&
+ h->primary_key_is_clustered()))
+ {
+ /* Use the default implementation */
+ return TRUE;
+ }
+
+ if (get_disk_sweep_mrr_cost(keyno, rows, *flags, bufsz, &dsmrr_cost))
+ return TRUE;
+
+ bool force_dsmrr;
+ /*
+ If @@optimizer_use_mrr==force, then set cost of DS-MRR to be minimum of
+ DS-MRR and Default implementations cost. This allows one to force use of
+ DS-MRR whenever it is applicable without affecting other cost-based
+ choices.
+ */
+ if ((force_dsmrr= (thd->variables.optimizer_use_mrr == 1)) &&
+ dsmrr_cost.total_cost() > cost->total_cost())
+ dsmrr_cost= *cost;
+
+ if (force_dsmrr || dsmrr_cost.total_cost() <= cost->total_cost())
+ {
+ *flags &= ~HA_MRR_USE_DEFAULT_IMPL; /* Use the DS-MRR implementation */
+ *flags &= ~HA_MRR_SORTED; /* We will return unordered output */
+ *cost= dsmrr_cost;
+ res= FALSE;
+ }
+ else
+ {
+ /* Use the default MRR implementation */
+ res= TRUE;
+ }
+ return res;
+}
+
+
+static void get_sort_and_sweep_cost(TABLE *table, ha_rows nrows, COST_VECT *cost);
+void get_sweep_read_cost(TABLE *table, ha_rows nrows, bool interrupted, COST_VECT *cost);
+
+/*
+ Get cost of DS-MRR scan
+
+ SYNOPSIS
+ get_disk_sweep_mrr_cost()
+ keynr Index to be used
+ rows E(Number of rows to be scanned)
+ flags Scan parameters (HA_MRR_* flags)
+ buffer_size INOUT Buffer size
+ cost OUT The cost
+
+ RETURN
+ FALSE OK
+ TRUE Error, DS-MRR cannot be used (the buffer is too small for even 1
+ rowid)
+*/
+
+bool DsMrr_impl::get_disk_sweep_mrr_cost(uint keynr, ha_rows rows, uint flags,
+ uint *buffer_size, COST_VECT *cost)
+{
+ ulong max_buff_entries, elem_size;
+ ha_rows rows_in_full_step, rows_in_last_step;
+ uint n_full_steps;
+ double index_read_cost;
+
+ elem_size= h->ref_length + sizeof(void*) * (!test(flags & HA_MRR_NO_ASSOCIATION));
+ max_buff_entries = *buffer_size / elem_size;
+
+ if (!max_buff_entries)
+ return TRUE; /* Buffer has not enough space for even 1 rowid */
+
+ /* Number of iterations we'll make with full buffer */
+ n_full_steps= (uint)floor(rows2double(rows) / max_buff_entries);
+
+ /*
+ Get numbers of rows we'll be processing in
+ - non-last sweep, with full buffer
+ - last iteration, with non-full buffer
+ */
+ rows_in_full_step= max_buff_entries;
+ rows_in_last_step= rows % max_buff_entries;
+
+ /* Adjust buffer size if we expect to use only part of the buffer */
+ if (n_full_steps)
+ {
+ get_sort_and_sweep_cost(h->table, rows, cost);
+ cost->multiply(n_full_steps);
+ }
+ else
+ {
+ cost->zero();
+ *buffer_size= rows_in_last_step * elem_size;
+ }
+
+ COST_VECT last_step_cost;
+ get_sort_and_sweep_cost(h->table, rows_in_last_step, &last_step_cost);
+ cost->add(&last_step_cost);
+
+ if (n_full_steps != 0)
+ cost->mem_cost= *buffer_size;
+ else
+ cost->mem_cost= rows_in_last_step * elem_size;
+
+ /* Total cost of all index accesses */
+ index_read_cost= h->index_only_read_time(keynr, rows);
+ cost->add_io(index_read_cost, 1 /* Random seeks */);
+ return FALSE;
+}
+
+
+/*
+ Get cost of one sort-and-sweep step
+
+ SYNOPSIS
+ get_sort_and_sweep_cost()
+ table Table being accessed
+ nrows Number of rows to be sorted and retrieved
+ cost OUT The cost
+
+ DESCRIPTION
+ Get cost of these operations:
+ - sort an array of #nrows ROWIDs using qsort
+ - read #nrows records from table in a sweep.
+*/
+
+static
+void get_sort_and_sweep_cost(TABLE *table, ha_rows nrows, COST_VECT *cost)
+{
+ if (nrows)
+ {
+ get_sweep_read_cost(table, nrows, FALSE, cost);
+ /* Add cost of qsort call: n * log2(n) * cost(rowid_comparison) */
+ double cmp_op= rows2double(nrows) * (1.0 / TIME_FOR_COMPARE_ROWID);
+ if (cmp_op < 3)
+ cmp_op= 3;
+ cost->cpu_cost += cmp_op * log2(cmp_op);
+ }
+ else
+ cost->zero();
+}
+
+
+/*
+ Get cost of reading nrows table records in a "disk sweep"
+
+ SYNOPSIS
+ get_sweep_read_cost()
+ table Table to be accessed
+ nrows Number of rows to retrieve
+ interrupted TRUE <=> Assume that the disk sweep will be
+ interrupted by other disk IO. FALSE - otherwise.
+ cost OUT The cost.
+
+ DESCRIPTION
+ Get cost of reading nrows table records in a "disk sweep" (This is done
+ by making a sequence of handler->rnd_pos() calls for ordered sequence of
+ rowids).
+*/
+
+void get_sweep_read_cost(TABLE *table, ha_rows nrows, bool interrupted, COST_VECT *cost)
+{
+ double result;
+ DBUG_ENTER("get_sweep_read_cost");
+
+ cost->zero();
+ if (table->file->primary_key_is_clustered())
+ {
+ cost->io_count= table->file->read_time(table->s->primary_key, nrows,
+ nrows);
+ }
+ else
+ {
+ double n_blocks=
+ ceil(ulonglong2double(table->file->data_file_length) / IO_SIZE);
+ double busy_blocks=
+ n_blocks * (1.0 - pow(1.0 - 1.0/n_blocks, rows2double(nrows)));
+ if (busy_blocks < 1.0)
+ busy_blocks= 1.0;
+ DBUG_PRINT("info",("sweep: nblocks=%g, busy_blocks=%g", n_blocks,
+ busy_blocks));
+ /*
+ Disabled: Bail out if # of blocks to read is bigger than # of blocks in
+ table data file.
+ if (max_cost != DBL_MAX && (busy_blocks+index_reads_cost) >= n_blocks)
+ return 1;
+ */
+ cost->io_count= busy_blocks;
+ if (!interrupted)
+ {
+ /* Assume reading is done in one 'sweep' */
+ cost->avg_io_cost= (DISK_SEEK_BASE_COST +
+ DISK_SEEK_PROP_COST*n_blocks/busy_blocks);
+ }
+ }
+ DBUG_PRINT("info",("returning cost=%g", cost->total_cost()));
+ DBUG_VOID_RETURN;
+}
+
+
+/****************************************************************************
+ * DS-MRR implementation ends
+ ***************************************************************************/
/*
--- 1.199/sql/handler.h 2006-03-18 14:44:55 +03:00
+++ 1.200/sql/handler.h 2006-05-22 19:02:31 +04:00
@@ -763,6 +763,29 @@
avg_io_cost= 1.0;
io_count= cpu_cost= mem_cost= import_cost= 0.0;
}
+
+ void multiply(double m)
+ {
+ io_count *= m;
+ cpu_cost *= m;
+ import_cost *= m;
+ /* Don't multiply mem_cost */
+ }
+
+ void add(const COST_VECT* cost)
+ {
+ double io_count_sum= io_count + cost->io_count;
+ add_io(cost->io_count, cost->avg_io_cost);
+ io_count= io_count_sum;
+ cpu_cost += cost->cpu_cost;
+ }
+ void add_io(double add_io_cnt, double add_avg_cost)
+ {
+ double io_count_sum= io_count + add_io_cnt;
+ avg_io_cost= (io_count * avg_io_cost +
+ add_io_cnt * add_avg_cost) / io_count_sum;
+ io_count= io_count_sum;
+ }
};
@@ -797,6 +820,14 @@
/*
+ Default MRR implementation is used
+ (The choice is made by **_info[_const]() function which may set this
+ flag. SQL layer remembers the flag value and then passes it to
+ multi_read_range_init().
+*/
+#define HA_MRR_USE_DEFAULT_IMPL 64
+
+/*
The handler class is the interface for dynamically loadable
storage engines. Do not add ifdefs and take care when adding or
changing virtual functions to avoid vtable confusion
@@ -804,6 +835,7 @@
class handler :public Sql_alloc
{
friend class ha_partition;
+ friend class DsMrr_impl;
protected:
struct st_table_share *table_share; /* The table definition */
@@ -855,7 +887,10 @@
bool mrr_have_range;
/* Current range (the one we're now returning rows from) */
KEY_MULTI_RANGE mrr_cur_range;
-
+
+ /* Default MRR implementation: */
+ bool mrr_restore_scan; /* TRUE <=> we're restoring the scan */
+ int mrr_restore_scan_res; /* iff mrr_restore_scan: return value of next call */
/* The following are for read_range() */
key_range save_end_range, *end_range;
@@ -882,7 +917,7 @@
ref(0), data_file_length(0), max_data_file_length(0), index_file_length(0),
delete_length(0), auto_increment_value(0),
records(0), deleted(0), mean_rec_length(0),
- create_time(0), check_time(0), update_time(0),
+ create_time(0), check_time(0), update_time(0), mrr_restore_scan(FALSE),
key_used_on_scan(MAX_KEY), active_index(MAX_KEY),
ref_length(sizeof(my_off_t)), block_size(0),
ft_handler(0), inited(NONE), implicit_emptied(0),
@@ -1570,7 +1605,46 @@
}
};
- /* Some extern variables used with handlers */
+class handler;
+class DsMrr_impl
+{
+public:
+ DsMrr_impl(handler *handler_par) : h(handler_par) {};
+ byte *rowids_buf; // rows buffer
+
+ byte *rowids_buf_cur; // current position in rowids buffer
+ byte *rowids_buf_last; // just-after-the-end position in rowids buffer
+ byte *rowids_buf_end;
+
+ KEY *mrr_key;
+ byte *last_idx_tuple;
+ uint mrr_keyno;
+ bool dsmrr_eof;
+
+ bool is_mrr_assoc; // TRUE <=> need mrr association (and the buffer holds
+ // {rowid, range_id} pairs
+ /* Some extern variables used with handlers */
+ handler *h;
+ bool use_default_impl;
+
+ int dsmrr_init(handler *h, KEY *key, RANGE_SEQ_IF *seq_funcs,
+ void *seq_init_param, uint n_ranges, uint mode,
+ HANDLER_BUFFER *buf);
+ int dsmrr_fill_buffer(handler *h);
+ int dsmrr_next(handler *h, char **range_info);
+
+ int dsmrr_info(uint keyno, uint n_ranges, uint keys, uint *bufsz,
+ uint *flags, COST_VECT *cost);
+
+ ha_rows dsmrr_info_const(uint keyno, RANGE_SEQ_IF *seq,
+ void *seq_init_param, uint n_ranges, uint *bufsz,
+ uint *flags, COST_VECT *cost);
+private:
+ bool choose_mrr_impl(uint keyno, ha_rows rows, uint *flags, uint *bufsz,
+ COST_VECT *cost);
+ bool get_disk_sweep_mrr_cost(uint keynr, ha_rows rows, uint flags,
+ uint *buffer_size, COST_VECT *cost);
+};
extern handlerton *sys_table_types[];
extern const char *ha_row_type[];
--- 1.39/sql/key.cc 2005-11-23 23:47:27 +03:00
+++ 1.40/sql/key.cc 2006-05-22 19:02:31 +04:00
@@ -150,6 +150,30 @@
/*
+ Zero the null components of key tuple
+ SYNOPSIS
+ key_zero_nulls()
+ tuple
+ key_info
+
+ DESCRIPTION
+
+*/
+
+void key_zero_nulls(byte *tuple, KEY *key_info)
+{
+ KEY_PART_INFO *key_part= key_info->key_part;
+ KEY_PART_INFO *key_part_end= key_part + key_info->key_parts;
+ for (; key_part != key_part_end; key_part++)
+ {
+ if (key_part->null_bit && *tuple)
+ bzero(tuple+1, key_part->store_length-1);
+ tuple+= key_part->store_length;
+ }
+}
+
+
+/*
Restore a key from some buffer to record.
SYNOPSIS
--- 1.385/sql/mysql_priv.h 2006-03-04 11:51:55 +03:00
+++ 1.386/sql/mysql_priv.h 2006-05-22 19:02:31 +04:00
@@ -1185,6 +1185,7 @@
/* key.cc */
int find_ref_key(KEY *key, uint key_count, Field *field, uint *key_length);
void key_copy(byte *to_key, byte *from_record, KEY *key_info, uint key_length);
+void key_zero_nulls(byte *tuple, KEY *key_info);
void key_restore(byte *to_record, byte *from_key, KEY *key_info,
uint key_length);
bool key_cmp_if_same(TABLE *form,const byte *key,uint index,uint key_length);
--- 1.542/sql/mysqld.cc 2006-03-04 11:20:39 +03:00
+++ 1.543/sql/mysqld.cc 2006-05-22 19:02:31 +04:00
@@ -6156,8 +6156,8 @@
"When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks. If not set, then it's set to the value of record_buffer.",
(gptr*) &global_system_variables.read_rnd_buff_size,
(gptr*) &max_system_variables.read_rnd_buff_size, 0,
- GET_ULONG, REQUIRED_ARG, 256*1024L, IO_SIZE*2+MALLOC_OVERHEAD,
- ~0L, MALLOC_OVERHEAD, IO_SIZE, 0},
+ GET_ULONG, REQUIRED_ARG, 256*1024L, 64 /*IO_SIZE*2+MALLOC_OVERHEAD*/ ,
+ ~0L, MALLOC_OVERHEAD, 1 /* <-- psergey */, 0},
{"record_buffer", OPT_RECORD_BUFFER,
"Alias for read_buffer_size",
(gptr*) &global_system_variables.read_buff_size,
--- 1.206/sql/opt_range.cc 2006-03-18 14:44:56 +03:00
+++ 1.207/sql/opt_range.cc 2006-05-22 19:02:31 +04:00
@@ -60,6 +60,49 @@
Record retrieval code for range/index_merge/groupby-min-max.
Implementations of QUICK_*_SELECT classes.
+
+ KeyTupleFormat
+ ~~~~~~~~~~~~~~
+ The code in this file (and elsewhere) makes operations on key value tuples.
+ Those tuples are stored in the following format:
+
+ The tuple is a sequence of key part values. The length of key part value
+ depends only on its type (and not depends on the what value is stored)
+ //unless it is prefix! The prefix may be for ???
+
+ KeyTuple: keypart1-data, keypart2-data, ...
+
+ The value of each keypart is stored in the following format:
+
+ keypart_data: [isnull_byte] keypart-value-bytes
+
+ If a keypart may have a NULL value (key_part->field->real_maybe_null() can
+ be used to check this), then the first byte is a NULL indicator with the
+ following valid values:
+ 1 - keypart has NULL value.
+ 0 - keypart has non-NULL value.
+
+ <questionable-statement> If isnull_byte==1 (NULL value), then the following
+ keypart->length bytes must be 0.
+ </questionable-statement>
+
+ keypart-value-bytes holds the value. Its format depends on the field type.
+ The length of keypart-value-bytes may or may not depend on the value being
+ stored. The default is that length is static and equal to
+ KEY_PART_INFO::length.
+
+ Key parts with (key_part_flag & HA_BLOB_PART) have length depending of the
+ value:
+
+ keypart-value-bytes: value_length value_bytes
+
+ The value_length part itself occupies HA_KEY_BLOB_LENGTH=2 bytes.
+
+ See key_copy() and key_restore() for code to move data between index tuple
+ in some buffer and table->record[0].
+
+ CAUTION: the above description is only psergey's understanding of the
+ subject and may omit some details.
*/
#ifdef USE_PRAGMA_IMPLEMENTATION
@@ -489,7 +532,9 @@
static SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param,COND *cond);
static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts);
-static ha_rows check_quick_select(PARAM *param,uint index,SEL_ARG *key_tree);
+static ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
+ SEL_ARG *tree, bool *native_mrr,
+ COST_VECT *cost);
QUICK_RANGE_SELECT *get_quick_select(PARAM *param,uint index,
SEL_ARG *key_tree,
@@ -832,7 +877,8 @@
QUICK_RANGE_SELECT::QUICK_RANGE_SELECT(THD *thd, TABLE *table, uint key_nr,
bool no_alloc, MEM_ROOT *parent_alloc)
- :dont_free(0),error(0),free_file(0),in_range(0),cur_range(NULL),range(0)
+ : dont_free(0),error(0),free_file(0),in_range(0),cur_range(NULL),range(0),
+ mrr_use_native(TRUE)
{
sorted= 0;
index= key_nr;
@@ -1636,9 +1682,10 @@
public:
SEL_ARG *key; /* set of intervals to be used in "range" method retrieval */
uint key_idx; /* key number in PARAM::key */
+ bool mrr_use_native; /* Whether native or default MRR is used */
- TRP_RANGE(SEL_ARG *key_arg, uint idx_arg)
- : key(key_arg), key_idx(idx_arg)
+ TRP_RANGE(SEL_ARG *key_arg, uint idx_arg, bool use_native_arg)
+ : key(key_arg), key_idx(idx_arg), mrr_use_native(use_native_arg)
{}
virtual ~TRP_RANGE() {} /* Remove gcc warning */
@@ -1651,6 +1698,7 @@
{
quick->records= records;
quick->read_time= read_cost;
+ quick->mrr_use_native= mrr_use_native;
}
DBUG_RETURN(quick);
}
@@ -4377,8 +4425,6 @@
Best range read plan
NULL if no plan found or error occurred
*/
-ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, SEL_ARG *tree,
- COST_VECT *cost);
static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
bool index_read_must_be_used,
@@ -4387,6 +4433,7 @@
int idx;
SEL_ARG **key,**end, **key_to_read= NULL;
ha_rows best_records;
+ bool best_uses_native_mrr;
TRP_RANGE* read_plan= NULL;
bool pk_is_clustered= param->table->file->primary_key_is_clustered();
DBUG_ENTER("get_key_scans_params");
@@ -4415,8 +4462,9 @@
(bool) param->table->used_keys.is_set(keynr);
COST_VECT cost;
+ bool native_mrr;
found_records= check_quick_select(param, idx, read_index_only, *key,
- &cost);
+ &native_mrr, &cost);
found_read_time= cost.total_cost();
if ((found_records != HA_POS_ERROR) && param->is_ror_scan)
{
@@ -4429,6 +4477,7 @@
read_time= found_read_time;
best_records= found_records;
key_to_read= key;
+ best_uses_native_mrr= native_mrr;
}
}
}
@@ -4438,7 +4487,8 @@
if (key_to_read)
{
idx= key_to_read - tree->keys;
- if ((read_plan= new (param->mem_root) TRP_RANGE(*key_to_read, idx)))
+ if ((read_plan= new (param->mem_root) TRP_RANGE(*key_to_read, idx,
+ best_uses_native_mrr)))
{
read_plan->records= best_records;
read_plan->is_ror= tree->ror_scans_map.is_set(idx);
@@ -6410,7 +6460,9 @@
} RANGE_SEQ_ENTRY;
-/* MRR range sequence, SEL_ARG* implementation: enumeration context */
+/*
+ MRR range sequence, SEL_ARG* implementation: SEL_ARG graph traversal context
+*/
typedef struct st_sel_arg_range_seq
{
uint keyno; /* index of used tree in SEL_TREE structure */
@@ -6420,7 +6472,7 @@
SEL_ARG *start; /* Root node of the traversed SEL_ARG* graph */
RANGE_SEQ_ENTRY stack[MAX_REF_PARTS];
- int i; //todo:psergey:
+ int i; /* Index of last used element in the above array */
bool at_start; /* TRUE <=> The traversal has just started */
} SEL_ARG_RANGE_SEQ;
@@ -6659,8 +6711,10 @@
HA_POS_ERROR if estimate calculation failed due to table handler problems.
*/
+
+static
ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, SEL_ARG *tree,
- COST_VECT *cost)
+ bool *native_mrr, COST_VECT *cost)
{
DBUG_ENTER("check_quick_select");
SEL_ARG_RANGE_SEQ seq;
@@ -6710,7 +6764,10 @@
(file->index_flags(keynr, param->max_key_part, 1) & HA_KEYREAD_ONLY) &&
!(pk_is_clustered && keynr == param->table->s->primary_key))
flags |= HA_MRR_INDEX_ONLY;
-
+
+ if (current_thd->lex->sql_command != SQLCOM_SELECT)
+ flags |= HA_MRR_USE_DEFAULT_IMPL;
+ bufsize= param->thd->variables.read_rnd_buff_size;
rows= file->multi_range_read_info_const(param->real_keynr[idx], &seq_if,
(void*)&seq,
0, // todo: n_ranges
@@ -6720,6 +6777,7 @@
param->table->quick_keys.set_bit(seq.real_keyno);
param->table->quick_rows[seq.real_keyno]=rows;
param->table->quick_key_parts[seq.real_keyno]=param->max_key_part+1;
+ *native_mrr= test(flags & HA_MRR_USE_DEFAULT_IMPL);
if (cpk_scan)
param->is_ror_scan= TRUE;
@@ -7544,8 +7602,13 @@
}
end:
RANGE_SEQ_IF seq_funcs= {quick_range_seq_init, quick_range_seq_next};
- file->multi_range_read_init(&seq_funcs, (void*)this, ranges.elements,
- (sorted?HA_MRR_SORTED:0) | HA_MRR_NO_ASSOCIATION, multi_range_buff);
+ uint flags= HA_MRR_NO_ASSOCIATION | (sorted ? HA_MRR_SORTED : 0);
+
+ if (mrr_use_native)
+ flags |= HA_MRR_USE_DEFAULT_IMPL;
+
+ file->multi_range_read_init(&seq_funcs, (void*)this, ranges.elements,
+ flags, multi_range_buff);
DBUG_RETURN(0);
}
@@ -8718,11 +8781,13 @@
cur_index_tree= get_index_range_tree(cur_index, tree, param,
&cur_param_idx);
/* Check if this range tree can be used for prefix retrieval. */
- COST_VECT dummy;
+ bool dummy1;
+ COST_VECT dummy2;
+ //psergey:todo: see if we can replace this: v v
cur_quick_prefix_records= check_quick_select(param, cur_param_idx,
- FALSE /*don't care*/,
- cur_index_tree,
- &dummy);
+ FALSE /*don't care*/,
+ cur_index_tree,
+ &dummy1, &dummy2);
}
cost_group_min_max(table, cur_index_info, used_key_parts,
cur_group_key_parts, tree, cur_index_tree,
--- 1.61/sql/opt_range.h 2006-03-18 14:44:57 +03:00
+++ 1.62/sql/opt_range.h 2006-05-22 19:02:31 +04:00
@@ -321,11 +321,13 @@
QUICK_RANGE *range;
KEY_PART *key_parts;
KEY_PART_INFO *key_part_info;
+
int cmp_next(QUICK_RANGE *range);
int cmp_prev(QUICK_RANGE *range);
bool row_in_ranges();
public:
MEM_ROOT alloc;
+ bool mrr_use_native;
QUICK_RANGE_SELECT(THD *thd, TABLE *table,uint index_arg,bool no_alloc=0,
MEM_ROOT *parent_alloc=NULL);
--- 1.287/sql/sql_class.h 2006-03-08 08:55:11 +03:00
+++ 1.288/sql/sql_class.h 2006-05-22 19:02:31 +04:00
@@ -207,6 +207,13 @@
ulong net_write_timeout;
ulong optimizer_prune_level;
ulong optimizer_search_depth;
+ /*
+ Controls use of Engine-MRR:
+ 0 - auto, based on cost
+ 1 - force MRR when the storage engine is capable of doing it
+ 2 - disable MRR.
+ */
+ ulong optimizer_use_mrr;
ulong preload_buff_size;
ulong query_cache_type;
ulong read_buff_size;
--- 1.392/sql/sql_select.cc 2006-03-18 14:44:57 +03:00
+++ 1.393/sql/sql_select.cc 2006-05-22 19:02:31 +04:00
@@ -11087,6 +11087,33 @@
}
+/*
+ Extract a condition that can be checked after reading given table
+
+ SYNOPSIS
+ make_cond_for_table()
+ cond Condition to analyze
+ tables Tables for which "current field values" are available
+ used_table Table that we're extracting the condition for
+
+ DESCRIPTION
+ Extract the condition that can be checked after reading the table
+ specified in 'used_table', given that current-field values for tables
+ specified in 'tables' bitmap are available.
+
+ The function assumes that
+ - Constant parts of the condition has already been checked.
+ - Condition that could be checked for tables in 'tables' has already
+ been checked.
+
+ The function takes into account that some parts of the condition are
+ guaranteed to be true by employed 'ref' access methods (the code that
+ does this is located at the end, search down for "EQ_FUNC").
+
+ RETURN
+ Extracted condition
+*/
+
static COND *
make_cond_for_table(COND *cond, table_map tables, table_map used_table)
{
@@ -11182,6 +11209,7 @@
return cond;
}
+
static Item *
part_of_refkey(TABLE *table,Field *field)
{
@@ -14127,6 +14155,7 @@
item_list.push_back(new Item_int((longlong) (ulonglong)
join->best_positions[i]. records_read,
21));
+
/* Build "Extra" field and add it to item_list. */
my_bool key_read=table->key_read;
if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
@@ -14184,6 +14213,13 @@
}
if (table->reginfo.not_exists_optimize)
extra.append(STRING_WITH_LEN("; Not exists"));
+
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
+ !((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_use_native)
+ {
+ extra.append(STRING_WITH_LEN("; Using MRR"));
+ }
+
if (need_tmp_table)
{
need_tmp_table=0;
--- 1.58/sql/structs.h 2006-01-07 16:41:42 +03:00
+++ 1.59/sql/structs.h 2006-05-22 19:02:31 +04:00
@@ -70,7 +70,9 @@
Field *field;
uint offset; /* offset in record (from 0) */
uint null_offset; /* Offset to null_bit in record */
- uint16 length; /* Length of key_part */
+ /* Length of key part in bytes, excluding NULL flag and length bytes */
+ uint16 length;
+ /* Max. length of the key part, including the NULL flag and length bytes. */
uint16 store_length;
uint16 key_type;
uint16 fieldnr; /* Fieldnum in UNIREG */
--- 1.134/sql/table.h 2006-03-07 22:00:44 +03:00
+++ 1.135/sql/table.h 2006-05-22 19:02:31 +04:00
@@ -233,7 +233,9 @@
byte *write_row_record; /* Used as optimisation in
THD::write_row */
byte *insert_values; /* used by INSERT ... UPDATE */
- key_map quick_keys, used_keys, keys_in_use_for_query;
+ key_map quick_keys;
+ key_map used_keys; /* Indexes that cover all fields used by the query */
+ key_map keys_in_use_for_query;
KEY *key_info; /* data of keys in database */
Field *next_number_field; /* Set if next_number is activated */
--- 1.284/sql/ha_ndbcluster.cc 2006-03-18 14:44:53 +03:00
+++ 1.285/sql/ha_ndbcluster.cc 2006-05-22 19:02:30 +04:00
@@ -7021,6 +7021,42 @@
* MRR interface implementation
***************************************************************************/
+ha_rows
+ha_ndbcluster::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
+ void *seq_init_param,
+ uint n_ranges, uint *bufsz,
+ uint *flags, COST_VECT *cost)
+{
+ ha_rows res;
+ res= handler::multi_range_read_info_const(keyno, seq, seq_init_param,
+ n_ranges, bufsz, flags, cost);
+
+ if (uses_blob_value())
+ {
+ /* NOTE: the above criteria doesn't match... */
+ *flags |= HA_MRR_USE_DEFAULT_IMPL;
+ }
+ else
+ *flags &= ~HA_MRR_USE_DEFAULT_IMPL;
+ return res;
+}
+
+int
+ha_ndbcluster::multi_range_read_info(uint keyno, uint n_ranges, uint keys,
+ uint *bufsz, uint *flags, COST_VECT *cost)
+{
+ int res;
+ res= handler::multi_range_read_info(keyno, n_ranges, keys, bufsz, flags,
+ cost);
+ if (uses_blob_value())
+ {
+ *flags &= ~HA_MRR_USE_DEFAULT_IMPL;
+ }
+ else
+ *flags &= ~HA_MRR_USE_DEFAULT_IMPL;
+ return res;
+}
+
#if 0
#define DBUG_MULTI_RANGE(x) DBUG_PRINT("info", ("multi_range_read_next: case %d\n", x));
#else
--- 1.124/sql/ha_ndbcluster.h 2006-03-18 14:44:54 +03:00
+++ 1.125/sql/ha_ndbcluster.h 2006-05-22 19:02:31 +04:00
@@ -584,6 +584,12 @@
int multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param,
uint n_ranges, uint mode, HANDLER_BUFFER *buf);
int multi_range_read_next(char **range_info);
+ ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
+ void *seq_init_param,
+ uint n_ranges, uint *bufsz,
+ uint *flags, COST_VECT *cost);
+ int multi_range_read_info(uint keyno, uint n_ranges, uint keys,
+ uint *bufsz, uint *flags, COST_VECT *cost);
private:
uint first_running_range;
uint first_range_in_batch;
--- 1.261/sql/ha_innodb.cc 2006-03-08 17:21:03 +03:00
+++ 1.262/sql/ha_innodb.cc 2006-05-22 19:02:30 +04:00
@@ -840,10 +840,13 @@
HA_PRIMARY_KEY_ALLOW_RANDOM_ACCESS |
HA_PRIMARY_KEY_IN_READ_INDEX |
HA_CAN_GEOMETRY |
- HA_TABLE_SCAN_ON_INDEX),
+ HA_TABLE_SCAN_ON_INDEX |
+ HA_NEED_READ_RANGE_BUFFER
+ ),
last_dup_key((uint) -1),
start_of_scan(0),
- num_write_row(0)
+ num_write_row(0),
+ ds_mrr(this)
{}
/*************************************************************************
@@ -7569,5 +7572,42 @@
return COMPATIBLE_DATA_NO;
return COMPATIBLE_DATA_YES;
+}
+
+
+/****************************************************************************
+ * DS-MRR implementation
+ ***************************************************************************/
+
+/**
+ * Multi Range Read interface, DS-MRR calls
+ */
+
+int ha_innobase::multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param,
+ uint n_ranges, uint mode, HANDLER_BUFFER *buf)
+{
+ return ds_mrr.dsmrr_init(this, &table->key_info[active_index],
+ seq, seq_init_param, n_ranges, mode, buf);
+}
+
+int ha_innobase::multi_range_read_next(char **range_info)
+{
+ return ds_mrr.dsmrr_next(this, range_info);
+}
+
+ha_rows ha_innobase::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
+ void *seq_init_param,
+ uint n_ranges, uint *bufsz,
+ uint *flags,
+ COST_VECT *cost)
+{
+ return ds_mrr.dsmrr_info_const(keyno, seq, seq_init_param, n_ranges, bufsz,
+ flags, cost);
+}
+
+int ha_innobase::multi_range_read_info(uint keyno, uint n_ranges, uint keys,
+ uint *bufsz, uint *flags, COST_VECT *cost)
+{
+ return ds_mrr.dsmrr_info(keyno, n_ranges, keys, bufsz, flags, cost);
}
--- 1.117/sql/ha_innodb.h 2006-02-08 13:58:49 +03:00
+++ 1.118/sql/ha_innodb.h 2006-05-22 19:02:30 +04:00
@@ -210,6 +210,20 @@
int cmp_ref(const byte *ref1, const byte *ref2);
bool check_if_incompatible_data(HA_CREATE_INFO *info,
uint table_changes);
+public:
+ /**
+ * Multi Range Read interface
+ */
+ int multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param,
+ uint n_ranges, uint mode, HANDLER_BUFFER *buf);
+ int multi_range_read_next(char **range_info);
+ ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
+ void *seq_init_param,
+ uint n_ranges, uint *bufsz,
+ uint *flags, COST_VECT *cost);
+ int multi_range_read_info(uint keyno, uint n_ranges, uint keys,
+ uint *bufsz, uint *flags, COST_VECT *cost);
+ DsMrr_impl ds_mrr;
};
extern SHOW_VAR innodb_status_variables[];
--- 1.178/sql/set_var.cc 2006-03-04 11:51:55 +03:00
+++ 1.179/sql/set_var.cc 2006-05-22 19:02:31 +04:00
@@ -337,7 +337,6 @@
&SV::myisam_stats_method,
&myisam_stats_method_typelib,
NULL);
-
sys_var_thd_ulong sys_net_buffer_length("net_buffer_length",
&SV::net_buffer_length);
sys_var_thd_ulong sys_net_read_timeout("net_read_timeout",
@@ -353,10 +352,23 @@
sys_var_thd_bool sys_old_alter_table("old_alter_table",
&SV::old_alter_table);
sys_var_thd_bool sys_old_passwords("old_passwords", &SV::old_passwords);
+
sys_var_thd_ulong sys_optimizer_prune_level("optimizer_prune_level",
&SV::optimizer_prune_level);
sys_var_thd_ulong sys_optimizer_search_depth("optimizer_search_depth",
&SV::optimizer_search_depth);
+
+const char *optimizer_use_mrr_names[] = {"auto", "force", "disable", NullS};
+TYPELIB optimizer_use_mrr_typelib= {
+ array_elements(optimizer_use_mrr_names) - 1, "",
+ optimizer_use_mrr_names, NULL
+};
+
+sys_var_thd_enum sys_optimizer_use_mrr("optimizer_use_mrr",
+ &SV::optimizer_use_mrr,
+ &optimizer_use_mrr_typelib,
+ NULL);
+
sys_var_thd_ulong sys_preload_buff_size("preload_buffer_size",
&SV::preload_buff_size);
sys_var_thd_ulong sys_read_buff_size("read_buffer_size",
@@ -896,6 +908,7 @@
SHOW_SYS},
{sys_optimizer_search_depth.name,(char*) &sys_optimizer_search_depth,
SHOW_SYS},
+ {sys_optimizer_use_mrr.name, (char*) &sys_optimizer_use_mrr, SHOW_SYS},
{"pid_file", (char*) pidfile_name, SHOW_CHAR},
{"plugin_dir", (char*) opt_plugin_dir, SHOW_CHAR},
{"port", (char*) &mysqld_port, SHOW_INT},
--- 1.17/mysql-test/r/ndb_condition_pushdown.result 2005-11-04 23:09:53 +03:00
+++ 1.18/mysql-test/r/ndb_condition_pushdown.result 2006-05-22 19:02:30 +04:00
@@ -925,7 +925,7 @@
date_time != '1901-01-01 01:01:01'
order by auto;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range medium_index medium_index 3 NULL 3 Using where with pushed condition; Using filesort
+1 SIMPLE t1 range medium_index medium_index 3 NULL 3 Using where with pushed condition; Using MRR; Using filesort
select auto from t1 where
string != "aaaa" and
vstring != "aaaa" and
@@ -984,7 +984,7 @@
date_time > '1901-01-01 01:01:01'
order by auto;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range medium_index medium_index 3 NULL 3 Using where with pushed condition; Using filesort
+1 SIMPLE t1 range medium_index medium_index 3 NULL 3 Using where with pushed condition; Using MRR; Using filesort
select auto from t1 where
string > "aaaa" and
vstring > "aaaa" and
@@ -1043,7 +1043,7 @@
date_time >= '1901-01-01 01:01:01'
order by auto;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range medium_index medium_index 3 NULL 4 Using where with pushed condition; Using filesort
+1 SIMPLE t1 range medium_index medium_index 3 NULL 4 Using where with pushed condition; Using MRR; Using filesort
select auto from t1 where
string >= "aaaa" and
vstring >= "aaaa" and
@@ -1103,7 +1103,7 @@
date_time < '1904-04-04 04:04:04'
order by auto;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range medium_index medium_index 3 NULL 3 Using where with pushed condition; Using filesort
+1 SIMPLE t1 range medium_index medium_index 3 NULL 3 Using where with pushed condition; Using MRR; Using filesort
select auto from t1 where
string < "dddd" and
vstring < "dddd" and
@@ -1162,7 +1162,7 @@
date_time <= '1904-04-04 04:04:04'
order by auto;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range medium_index medium_index 3 NULL 4 Using where with pushed condition; Using filesort
+1 SIMPLE t1 range medium_index medium_index 3 NULL 4 Using where with pushed condition; Using MRR; Using filesort
select auto from t1 where
string <= "dddd" and
vstring <= "dddd" and
@@ -1255,7 +1255,7 @@
(date_time between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')
order by auto;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range medium_index medium_index 3 NULL 3 Using where with pushed condition; Using filesort
+1 SIMPLE t1 range medium_index medium_index 3 NULL 3 Using where with pushed condition; Using MRR; Using filesort
select auto from t1 where
(string between "aaaa" and "cccc") and
(vstring between "aaaa" and "cccc") and
@@ -1358,7 +1358,7 @@
(date_time not between '1901-01-01 01:01:01' and '1903-03-03 03:03:03')
order by auto;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range medium_index medium_index 3 NULL 1 Using where with pushed condition; Using filesort
+1 SIMPLE t1 range medium_index medium_index 3 NULL 1 Using where with pushed condition; Using MRR; Using filesort
select auto from t1 where
(string not between "aaaa" and "cccc") and
(vstring not between "aaaa" and "cccc") and
@@ -1462,7 +1462,7 @@
date_time in('1901-01-01 01:01:01','1903-03-03 03:03:03')
order by auto;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range medium_index medium_index 3 NULL 2 Using where with pushed condition; Using filesort
+1 SIMPLE t1 range medium_index medium_index 3 NULL 2 Using where with pushed condition; Using MRR; Using filesort
select auto from t1 where
string in("aaaa","cccc") and
vstring in("aaaa","cccc") and
@@ -1565,7 +1565,7 @@
date_time not in('1901-01-01 01:01:01','1903-03-03 03:03:03')
order by auto;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range medium_index medium_index 3 NULL 2 Using where with pushed condition; Using filesort
+1 SIMPLE t1 range medium_index medium_index 3 NULL 2 Using where with pushed condition; Using MRR; Using filesort
select auto from t1 where
string not in("aaaa","cccc") and
vstring not in("aaaa","cccc") and
@@ -1738,7 +1738,7 @@
explain
select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range attr1 attr1 4 NULL 5 Using where with pushed condition; Using filesort
+1 SIMPLE t4 range attr1 attr1 4 NULL 5 Using where with pushed condition; Using MRR; Using filesort
select * from t4 where attr1 < 5 and attr2 > 9223372036854775803 and attr3 != 3 order by t4.pk1;
pk1 attr1 attr2 attr3 attr4
2 2 9223372036854775804 2 c
@@ -1746,7 +1746,7 @@
explain
select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 range attr1 attr1 4 NULL 4 Using where with pushed condition; Using temporary; Using filesort
+1 SIMPLE t4 range attr1 attr1 4 NULL 4 Using where with pushed condition; Using MRR; Using temporary; Using filesort
1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where
select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
pk1 attr1 attr2 attr3 attr4 pk1 attr1 attr2 attr3 attr4
--- 1.17/mysql-test/r/ndb_blob.result 2006-02-09 13:34:34 +03:00
+++ 1.18/mysql-test/r/ndb_blob.result 2006-05-22 19:02:30 +04:00
@@ -242,7 +242,7 @@
commit;
explain select * from t1 where c >= 100 order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c c 4 NULL 9 Using where; Using filesort
+1 SIMPLE t1 range c c 4 NULL 9 Using where; Using MRR; Using filesort
select * from t1 where c >= 100 order by a;
a b c d
1 b1 111 dd1
@@ -278,7 +278,7 @@
commit;
explain select * from t1 where c >= 100 order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c c 4 NULL 2 Using where; Using filesort
+1 SIMPLE t1 range c c 4 NULL 2 Using where; Using MRR; Using filesort
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
from t1 where c >= 100 order by a;
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
--- New file ---
+++ mysql-test/include/mrr_tests.inc 06/05/22 19:02:31
create table t1(a int);
show create table t1;
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a int);
insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
set read_rnd_buffer_size=64;
select @@read_rnd_buffer_size;
create table t3 (
a char(8) not null, b char(8) not null, filler char(200),
key(a)
);
insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A;
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'),
'filler-1' from t2 A;
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'),
'filler-2' from t2 A;
# Test empty result set
select a,filler from t3 where a >= 'c-9011=w';
# Ok, t3.ref_length=6, limit is 64 => 10 elements fit into the buffer
# Test the cases when buffer gets exhausted at different points in source
# intervals:
# 1. Split is in the middle of the range
select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w';
# 2. Split is at range edge
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
(a>='c-1014=w' and a <= 'c-1015=w');
# 3. Split is at range edge, with some rows between ranges.
insert into t3 values ('c-1013=z', 'c-1013=z', 'err');
insert into t3 values ('a-1014=w', 'a-1014=w', 'err');
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
(a>='c-1014=w' and a <= 'c-1015=w');
delete from t3 where b in ('c-1013=z', 'a-1014=w');
# 4. Split is within the equality range.
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
a='c-1014=w' or a='c-1015=w';
# 5. Split is at the edge of equality range.
insert into t3 values ('c-1013=w', 'del-me', 'inserted');
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
a='c-1014=w' or a='c-1015=w';
delete from t3 where b='del-me';
# PK tests are not included here.
alter table t3 add primary key(b);
## PK scan tests
# 6. Split is between 'unique' PK ranges
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
b IN ('c-1019=w', 'c-1020=w', 'c-1021=w',
'c-1022=w', 'c-1023=w', 'c-1024=w');
# 7. Between non-uniq and uniq range
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or
b IN ('c-1021=w', 'c-1022=w', 'c-1023=w');
# 8. Between uniq and non-uniq range
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
b IN ('c-1019=w', 'c-1020=w') or
(b>='c-1021=w' and b<= 'c-1023=w');
## End of PK scan tests
#
# Now try different keypart types and special values
#
create table t4 (a varchar(10), b int, c char(10), filler char(200),
key idx1 (a, b, c));
# insert buffer_size * 1.5 all-NULL tuples
insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500;
explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
--- New file ---
+++ mysql-test/r/innodb_mrr.result 06/05/22 19:02:32
drop table if exists t1,t2,t3,t4;
set @@optimizer_use_mrr='force';
set @save_storage_engine= @@storage_engine;
set storage_engine=InnoDB;
create table t1(a int);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a int);
insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
set read_rnd_buffer_size=64;
select @@read_rnd_buffer_size;
@@read_rnd_buffer_size
64
create table t3 (
a char(8) not null, b char(8) not null, filler char(200),
key(a)
);
insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A;
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'),
'filler-1' from t2 A;
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'),
'filler-2' from t2 A;
select a,filler from t3 where a >= 'c-9011=w';
a filler
select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w';
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
(a>='c-1014=w' and a <= 'c-1015=w');
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
insert into t3 values ('c-1013=z', 'c-1013=z', 'err');
insert into t3 values ('a-1014=w', 'a-1014=w', 'err');
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
(a>='c-1014=w' and a <= 'c-1015=w');
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
delete from t3 where b in ('c-1013=z', 'a-1014=w');
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
a='c-1014=w' or a='c-1015=w';
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
insert into t3 values ('c-1013=w', 'del-me', 'inserted');
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
a='c-1014=w' or a='c-1015=w';
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1013=w inserted
c-1014=w filler
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
delete from t3 where b='del-me';
alter table t3 add primary key(b);
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
b IN ('c-1019=w', 'c-1020=w', 'c-1021=w',
'c-1022=w', 'c-1023=w', 'c-1024=w');
b filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1015=w filler
c-1016=w filler
c-1017=w filler
c-1018=w filler
c-1019=w filler
c-1020=w filler
c-1021=w filler
c-1022=w filler
c-1023=w filler
c-1024=w filler
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or
b IN ('c-1021=w', 'c-1022=w', 'c-1023=w');
b filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1015=w filler
c-1016=w filler
c-1017=w filler
c-1018=w filler
c-1019=w filler
c-1020=w filler
c-1021=w filler
c-1022=w filler
c-1023=w filler
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
b IN ('c-1019=w', 'c-1020=w') or
(b>='c-1021=w' and b<= 'c-1023=w');
b filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1015=w filler
c-1016=w filler
c-1017=w filler
c-1018=w filler
c-1019=w filler
c-1020=w filler
c-1021=w filler
c-1022=w filler
c-1023=w filler
create table t4 (a varchar(10), b int, c char(10), filler char(200),
key idx1 (a, b, c));
insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500;
explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using where; Using MRR
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
NULL NULL NULL NULL-15
NULL NULL NULL NULL-14
NULL NULL NULL NULL-13
NULL NULL NULL NULL-12
NULL NULL NULL NULL-11
NULL NULL NULL NULL-10
NULL NULL NULL NULL-9
NULL NULL NULL NULL-8
NULL NULL NULL NULL-7
NULL NULL NULL NULL-6
NULL NULL NULL NULL-5
NULL NULL NULL NULL-4
NULL NULL NULL NULL-3
NULL NULL NULL NULL-2
NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using where; Using MRR
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
b-1 NULL c-1 NULL-14
b-1 NULL c-1 NULL-13
b-1 NULL c-1 NULL-12
b-1 NULL c-1 NULL-11
b-1 NULL c-1 NULL-10
b-1 NULL c-1 NULL-9
b-1 NULL c-1 NULL-8
b-1 NULL c-1 NULL-7
b-1 NULL c-1 NULL-6
b-1 NULL c-1 NULL-5
b-1 NULL c-1 NULL-4
b-1 NULL c-1 NULL-3
b-1 NULL c-1 NULL-2
b-1 NULL c-1 NULL-1
bb-1 NULL cc-2 NULL-15
bb-1 NULL cc-2 NULL-14
bb-1 NULL cc-2 NULL-13
bb-1 NULL cc-2 NULL-12
bb-1 NULL cc-2 NULL-11
bb-1 NULL cc-2 NULL-10
bb-1 NULL cc-2 NULL-9
bb-1 NULL cc-2 NULL-8
bb-1 NULL cc-2 NULL-7
bb-1 NULL cc-2 NULL-6
bb-1 NULL cc-2 NULL-5
bb-1 NULL cc-2 NULL-4
bb-1 NULL cc-2 NULL-3
bb-1 NULL cc-2 NULL-2
bb-1 NULL cc-2 NULL-1
select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
b-1 NULL c-1 NULL-14
b-1 NULL c-1 NULL-13
b-1 NULL c-1 NULL-12
b-1 NULL c-1 NULL-11
b-1 NULL c-1 NULL-10
b-1 NULL c-1 NULL-9
b-1 NULL c-1 NULL-8
b-1 NULL c-1 NULL-7
b-1 NULL c-1 NULL-6
b-1 NULL c-1 NULL-5
b-1 NULL c-1 NULL-4
b-1 NULL c-1 NULL-3
b-1 NULL c-1 NULL-2
b-1 NULL c-1 NULL-1
bb-1 NULL cc-2 NULL-15
bb-1 NULL cc-2 NULL-14
bb-1 NULL cc-2 NULL-13
bb-1 NULL cc-2 NULL-12
bb-1 NULL cc-2 NULL-11
bb-1 NULL cc-2 NULL-10
bb-1 NULL cc-2 NULL-9
bb-1 NULL cc-2 NULL-8
bb-1 NULL cc-2 NULL-7
bb-1 NULL cc-2 NULL-6
bb-1 NULL cc-2 NULL-5
bb-1 NULL cc-2 NULL-4
bb-1 NULL cc-2 NULL-3
bb-1 NULL cc-2 NULL-2
bb-1 NULL cc-2 NULL-1
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
set storage_engine= @save_storage_engine;
drop table t1, t2, t3, t4;
set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) ) engine=InnoDB;
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
explain
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range a a 9 NULL 99 Using where; Using MRR
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
create table t2 (a char(100), b char(100), c char(100), d int,
filler char(10), key(d), primary key (a,b,c)) engine= innodb;
insert into t2 select A.a, B.a, B.a, A.a, 'filler' from t1 A, t1 B;
explain select * from t2 force index (d) where d < 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range d d 5 NULL 47 Using where
drop table t2;
set @@optimizer_use_mrr='auto';
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
--- New file ---
+++ mysql-test/r/myisam_mrr.result 06/05/22 19:02:32
drop table if exists t1, t2, t3;
set @@optimizer_use_mrr='force';
create table t1(a int);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a int);
insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
set read_rnd_buffer_size=64;
select @@read_rnd_buffer_size;
@@read_rnd_buffer_size
64
create table t3 (
a char(8) not null, b char(8) not null, filler char(200),
key(a)
);
insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A;
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'),
'filler-1' from t2 A;
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'),
'filler-2' from t2 A;
select a,filler from t3 where a >= 'c-9011=w';
a filler
select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w';
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
(a>='c-1014=w' and a <= 'c-1015=w');
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
insert into t3 values ('c-1013=z', 'c-1013=z', 'err');
insert into t3 values ('a-1014=w', 'a-1014=w', 'err');
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
(a>='c-1014=w' and a <= 'c-1015=w');
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
delete from t3 where b in ('c-1013=z', 'a-1014=w');
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
a='c-1014=w' or a='c-1015=w';
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
insert into t3 values ('c-1013=w', 'del-me', 'inserted');
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
a='c-1014=w' or a='c-1015=w';
a filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1011=w filler-1
c-1012=w filler-1
c-1013=w filler-1
c-1011=w filler-2
c-1012=w filler-2
c-1013=w filler-2
c-1013=w inserted
c-1014=w filler
c-1015=w filler
c-1014=w filler-1
c-1015=w filler-1
c-1014=w filler-2
c-1015=w filler-2
delete from t3 where b='del-me';
alter table t3 add primary key(b);
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
b IN ('c-1019=w', 'c-1020=w', 'c-1021=w',
'c-1022=w', 'c-1023=w', 'c-1024=w');
b filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1015=w filler
c-1016=w filler
c-1017=w filler
c-1018=w filler
c-1019=w filler
c-1020=w filler
c-1021=w filler
c-1022=w filler
c-1023=w filler
c-1024=w filler
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or
b IN ('c-1021=w', 'c-1022=w', 'c-1023=w');
b filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1015=w filler
c-1016=w filler
c-1017=w filler
c-1018=w filler
c-1019=w filler
c-1020=w filler
c-1021=w filler
c-1022=w filler
c-1023=w filler
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
b IN ('c-1019=w', 'c-1020=w') or
(b>='c-1021=w' and b<= 'c-1023=w');
b filler
c-1011=w filler
c-1012=w filler
c-1013=w filler
c-1014=w filler
c-1015=w filler
c-1016=w filler
c-1017=w filler
c-1018=w filler
c-1019=w filler
c-1020=w filler
c-1021=w filler
c-1022=w filler
c-1023=w filler
create table t4 (a varchar(10), b int, c char(10), filler char(200),
key idx1 (a, b, c));
insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15;
insert into t4 (a,b,c,filler)
select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500;
explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using where; Using MRR
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
NULL NULL NULL NULL-15
NULL NULL NULL NULL-14
NULL NULL NULL NULL-13
NULL NULL NULL NULL-12
NULL NULL NULL NULL-11
NULL NULL NULL NULL-10
NULL NULL NULL NULL-9
NULL NULL NULL NULL-8
NULL NULL NULL NULL-7
NULL NULL NULL NULL-6
NULL NULL NULL NULL-5
NULL NULL NULL NULL-4
NULL NULL NULL NULL-3
NULL NULL NULL NULL-2
NULL NULL NULL NULL-1
explain
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using where; Using MRR
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
b-1 NULL c-1 NULL-14
b-1 NULL c-1 NULL-13
b-1 NULL c-1 NULL-12
b-1 NULL c-1 NULL-11
b-1 NULL c-1 NULL-10
b-1 NULL c-1 NULL-9
b-1 NULL c-1 NULL-8
b-1 NULL c-1 NULL-7
b-1 NULL c-1 NULL-6
b-1 NULL c-1 NULL-5
b-1 NULL c-1 NULL-4
b-1 NULL c-1 NULL-3
b-1 NULL c-1 NULL-2
b-1 NULL c-1 NULL-1
bb-1 NULL cc-2 NULL-15
bb-1 NULL cc-2 NULL-14
bb-1 NULL cc-2 NULL-13
bb-1 NULL cc-2 NULL-12
bb-1 NULL cc-2 NULL-11
bb-1 NULL cc-2 NULL-10
bb-1 NULL cc-2 NULL-9
bb-1 NULL cc-2 NULL-8
bb-1 NULL cc-2 NULL-7
bb-1 NULL cc-2 NULL-6
bb-1 NULL cc-2 NULL-5
bb-1 NULL cc-2 NULL-4
bb-1 NULL cc-2 NULL-3
bb-1 NULL cc-2 NULL-2
bb-1 NULL cc-2 NULL-1
select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
a b c filler
b-1 NULL c-1 NULL-15
b-1 NULL c-1 NULL-14
b-1 NULL c-1 NULL-13
b-1 NULL c-1 NULL-12
b-1 NULL c-1 NULL-11
b-1 NULL c-1 NULL-10
b-1 NULL c-1 NULL-9
b-1 NULL c-1 NULL-8
b-1 NULL c-1 NULL-7
b-1 NULL c-1 NULL-6
b-1 NULL c-1 NULL-5
b-1 NULL c-1 NULL-4
b-1 NULL c-1 NULL-3
b-1 NULL c-1 NULL-2
b-1 NULL c-1 NULL-1
bb-1 NULL cc-2 NULL-15
bb-1 NULL cc-2 NULL-14
bb-1 NULL cc-2 NULL-13
bb-1 NULL cc-2 NULL-12
bb-1 NULL cc-2 NULL-11
bb-1 NULL cc-2 NULL-10
bb-1 NULL cc-2 NULL-9
bb-1 NULL cc-2 NULL-8
bb-1 NULL cc-2 NULL-7
bb-1 NULL cc-2 NULL-6
bb-1 NULL cc-2 NULL-5
bb-1 NULL cc-2 NULL-4
bb-1 NULL cc-2 NULL-3
bb-1 NULL cc-2 NULL-2
bb-1 NULL cc-2 NULL-1
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
set @@optimizer_use_mrr='auto';
drop table t1, t2, t3, t4;
--- New file ---
+++ mysql-test/t/innodb_mrr.test 06/05/22 19:02:33
-- source include/have_innodb.inc
--disable_warnings
drop table if exists t1,t2,t3,t4;
--enable_warnings
set @@optimizer_use_mrr='force';
set @save_storage_engine= @@storage_engine;
set storage_engine=InnoDB;
--source include/mrr_tests.inc
set storage_engine= @save_storage_engine;
drop table t1, t2, t3, t4;
# Try big rowid sizes
set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
set read_rnd_buffer_size=64;
# By default InnoDB will fill values only for key parts used by the query,
# which will cause DS-MRR to supply an invalid tuple on scan restoration.
# Verify that DS-MRR's code extra(HA_EXTRA_RETRIEVE_ALL_COLS) call has effect:
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) ) engine=InnoDB;
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
explain
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
drop table t2;
# Try a very big rowid
create table t2 (a char(100), b char(100), c char(100), d int,
filler char(10), key(d), primary key (a,b,c)) engine= innodb;
insert into t2 select A.a, B.a, B.a, A.a, 'filler' from t1 A, t1 B;
explain select * from t2 force index (d) where d < 10;
drop table t2;
set @@optimizer_use_mrr='auto';
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
--- New file ---
+++ mysql-test/t/myisam_mrr.test 06/05/22 19:02:33
#
# MRR/MyISAM tests.
#
--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings
set @@optimizer_use_mrr='force';
-- source include/mrr_tests.inc
set @@optimizer_use_mrr='auto';
drop table t1, t2, t3, t4;
| Thread |
|---|
| • bk commit into 5.2 tree (sergefp:1.2155) | Sergey Petrunia | 22 May |
