November 2012 - developers - lists.mariadb.org

Re: [Maria-developers] Fwd: [Commits] Rev 3479: MDEV-3858 Change JOIN_TAB::records_read from ha_rows to double in file:///home/tsk/mprog/src/10.0-md3858/
by Sergei Petrunia 15 Nov '12

15 Nov '12

Hi Timour, Ok to push. On Thu, Nov 15, 2012 at 12:56:59PM +0200, Timour Katchaounov wrote: > Sergey, > > Could you please review this trivial task, shouldn't take more than 10 min. > > Timour > > ------------------------------------------------------------ > revno: 3479 > revision-id: timour(a)askmonty.org-20121115105450-zkiva73utg8pg49j > parent: wlad(a)montyprogram.com-20121111095852-1jsqvi3zcdml80lt > fixes bug: https://mariadb.atlassian.net/browse/MDEV-3858 > committer: timour(a)askmonty.org > branch nick: 10.0-md3858 > timestamp: Thu 2012-11-15 12:54:50 +0200 > message: > MDEV-3858 Change JOIN_TAB::records_read from ha_rows to double > > Currently JOIN_TAB::records_read is of type ha_rows. > This is an integer type, which prevents proper selectivity and rows estimates. > > > === modified file 'mysql-test/r/subselect_sj_mat.result' > --- a/mysql-test/r/subselect_sj_mat.result 2012-07-25 16:53:49 +0000 > +++ b/mysql-test/r/subselect_sj_mat.result 2012-11-15 10:54:50 +0000 > @@ -94,7 +94,7 @@ a1 a2 > explain extended > select * from t1i where a1 in (select b1 from t2i where b1 > '0'); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 40.00 Using where; Using index; LooseScan > +1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 50.00 Using where; Using index; LooseScan > 1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 > Warnings: > Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) > @@ -117,7 +117,7 @@ a1 a2 > explain extended > select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 40.00 Using where; Using index; LooseScan > +1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 50.00 Using where; Using index; LooseScan > 1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 > Warnings: > Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) > @@ -319,7 +319,7 @@ where (a1, a2) in (select b1, b2 from t2 > (a1, a2) in (select c1, c2 from t3i > where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 40.00 # > +1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # > 1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # > 1 PRIMARY t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # > 1 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # > @@ -407,7 +407,7 @@ id select_type table type possible_keys > 2 MATERIALIZED t2 ALL NULL # # # 5 100.00 # > 4 MATERIALIZED t3 ALL NULL # # # 4 100.00 # > 3 MATERIALIZED t3 ALL NULL # # # 4 100.00 # > -7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 40.00 # > +7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # > 7 UNION t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # > 7 UNION t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # > 7 UNION t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2012-11-03 11:28:51 +0000 > +++ b/sql/item_subselect.cc 2012-11-15 10:54:50 +0000 > @@ -4553,7 +4553,7 @@ double get_fanout_with_deps(JOIN *join, > !tab->emb_sj_nest && > tab->records_read != 0) > { > - fanout *= rows2double(tab->records_read); > + fanout *= tab->records_read; > } > } > return fanout; > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2012-11-04 15:32:32 +0000 > +++ b/sql/sql_select.cc 2012-11-15 10:54:50 +0000 > @@ -6789,7 +6789,7 @@ void JOIN::get_prefix_cost_and_fanout(ui > > double JOIN::get_examined_rows() > { > - ha_rows examined_rows; > + double examined_rows; > double prev_fanout= 1; > JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS); > JOIN_TAB *prev_tab= tab; > @@ -6799,7 +6799,7 @@ double JOIN::get_examined_rows() > while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab))) > { > prev_fanout *= prev_tab->records_read; > - examined_rows+= (ha_rows) (tab->get_examined_rows() * prev_fanout); > + examined_rows+= tab->get_examined_rows() * prev_fanout; > prev_tab= tab; > } > return examined_rows; > @@ -7704,7 +7704,8 @@ get_best_combination(JOIN *join) > sub-order > */ > SJ_MATERIALIZATION_INFO *sjm= cur_pos->table->emb_sj_nest->sj_mat_info; > - j->records= j->records_read= (ha_rows)(sjm->is_sj_scan? sjm->rows : 1); > + j->records_read= sjm->is_sj_scan? sjm->rows : 1; > + j->records= (ha_rows) j->records_read; > JOIN_TAB *jt; > JOIN_TAB_RANGE *jt_range; > if (!(jt= (JOIN_TAB*)join->thd->alloc(sizeof(JOIN_TAB)*sjm->tables)) || > @@ -7766,7 +7767,7 @@ get_best_combination(JOIN *join) > Save records_read in JOIN_TAB so that select_describe()/etc don't have > to access join->best_positions[]. > */ > - j->records_read= (ha_rows)join->best_positions[tablenr].records_read; > + j->records_read= join->best_positions[tablenr].records_read; > join->map2table[j->table->tablenr]= j; > > /* If we've reached the end of sjm nest, switch back to main sequence */ > @@ -10460,7 +10461,7 @@ double JOIN_TAB::scan_time() > > ha_rows JOIN_TAB::get_examined_rows() > { > - ha_rows examined_rows; > + double examined_rows; > > if (select && select->quick && use_quick != 2) > examined_rows= select->quick->records; > @@ -10490,7 +10491,7 @@ ha_rows JOIN_TAB::get_examined_rows() > } > } > else > - examined_rows= (ha_rows) records_read; > + examined_rows= records_read; > > return examined_rows; > } > @@ -21972,7 +21973,7 @@ int JOIN::print_explain(select_result_si > } > else > { > - ha_rows examined_rows= tab->get_examined_rows(); > + double examined_rows= tab->get_examined_rows(); > > item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, > MY_INT64_NUM_DECIMAL_DIGITS)); > @@ -21982,7 +21983,7 @@ int JOIN::print_explain(select_result_si > { > float f= 0.0; > if (examined_rows) > - f= (float) (100.0 * tab->records_read / examined_rows); > + f= (100.0 * (float)tab->records_read) / examined_rows; > set_if_smaller(f, 100.0); > item_list.push_back(new Item_float(f, 2)); > } > > === modified file 'sql/sql_select.h' > --- a/sql/sql_select.h 2012-11-04 15:32:32 +0000 > +++ b/sql/sql_select.h 2012-11-15 10:54:50 +0000 > @@ -280,8 +280,8 @@ typedef struct st_join_table { > */ > double read_time; > > - /* psergey-todo: make the below have type double, like POSITION::records_read? */ > - ha_rows records_read; > + /* Copy of POSITION::records_read, set by get_best_combination() */ > + double records_read; > > /* Startup cost for execution */ > double startup_cost; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] The patch for the task mdev-539
by Sergei Golubchik 13 Nov '12

13 Nov '12

Hi, Igor! Sorry for a delay :( Please, find my review attached. On Sep 27, Igor Babaev wrote: > === modified file 'mysql-test/r/alter_table.result' > --- a/mysql-test/r/alter_table.result 2012-05-21 13:30:25 +0000 > +++ b/mysql-test/r/alter_table.result 2012-09-27 21:29:26 +0000 > @@ -1340,3 +1340,203 @@ > execute stmt1; > deallocate prepare stmt1; > drop table t2; > +# > +# mdev-539: fast build of unique/primary indexes for MyISAM > +# I'd suggest to have these test cases in a separate file, say, alter_table_539.test But it's just a siggestion, do as you prefer. > +DROP DATABASE IF EXISTS dbt3_s001; > +CREATE DATABASE dbt3_s001; > +use dbt3_s001; > +drop index `primary` on customer; > +show create table customer; > +Table Create Table > +customer CREATE TABLE `customer` ( > + `c_custkey` int(11) NOT NULL, > + `c_name` varchar(25) DEFAULT NULL, > + `c_address` varchar(40) DEFAULT NULL, > + `c_nationkey` int(11) DEFAULT NULL, > + `c_phone` char(15) DEFAULT NULL, > + `c_acctbal` double DEFAULT NULL, > + `c_mktsegment` char(10) DEFAULT NULL, > + `c_comment` varchar(117) DEFAULT NULL, > + KEY `i_c_nationkey` (`c_nationkey`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +alter table customer add primary key (c_custkey); > +show create table customer; > +Table Create Table > +customer CREATE TABLE `customer` ( > + `c_custkey` int(11) NOT NULL, > + `c_name` varchar(25) DEFAULT NULL, > + `c_address` varchar(40) DEFAULT NULL, > + `c_nationkey` int(11) DEFAULT NULL, > + `c_phone` char(15) DEFAULT NULL, > + `c_acctbal` double DEFAULT NULL, > + `c_mktsegment` char(10) DEFAULT NULL, > + `c_comment` varchar(117) DEFAULT NULL, > + PRIMARY KEY (`c_custkey`), > + KEY `i_c_nationkey` (`c_nationkey`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +drop index `primary` on customer; > +insert into customer values > +(3,'Customer#00000000303','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg'); > +alter table customer add primary key (c_custkey); please add a test case for alter ignore table > +ERROR 23000: Duplicate entry '3' for key 'PRIMARY' > +show create table customer; > +Table Create Table > +customer CREATE TABLE `customer` ( ... > === modified file 'sql/ha_partition.cc' > --- a/sql/ha_partition.cc 2012-08-31 21:54:54 +0000 > +++ b/sql/ha_partition.cc 2012-09-27 21:29:26 +0000 > @@ -6862,6 +6862,8 @@ > > flags_to_return= ht->alter_table_flags(flags); > flags_to_return|= m_file[0]->alter_table_flags(flags); > + flags_to_return&= > + ~(HA_FAST_ADD_INDEX | HA_FAST_ADD_UNIQUE_INDEX | HA_FAST_ADD_PK_INDEX); You don't need these flags - see below > > /* > If one partition fails we must be able to revert the change for the other, > > === modified file 'sql/sql_table.cc' > --- a/sql/sql_table.cc 2012-09-02 02:41:38 +0000 > +++ b/sql/sql_table.cc 2012-09-27 21:29:26 +0000 > @@ -6743,6 +6745,11 @@ > my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0)); > goto err_new_table_cleanup; > }); > + > + if (!ignore && > + test(alter_flags & (HA_FAST_ADD_UNIQUE_INDEX | HA_FAST_ADD_PK_INDEX))) > + new_table->file->extra(HA_EXTRA_CREATE_UNIQUE_INDEX_BY_SORT); What's the point? extra() functions were always *optional* in the storage engine API, any storage engine is allowed to ignore any HA_EXTRA_* requests, or all of them. It means, you don't need to introduce HA_FAST_whatever flags, and don't need to check them. Just write if (!ignore) new_table->file->extra(HA_EXTRA_CREATE_UNIQUE_INDEX_BY_SORT); and let the engine ignore it, if it wishes to do so. as a small bonus, you won't need to move alter_flags variable around, it can stay where it was, inside the if() block. > + > error= copy_data_between_tables(thd, table, new_table, > alter_info->create_list, ignore, > order_num, order, &copied, &deleted, > > === modified file 'storage/maria/ha_maria.cc' > --- a/storage/maria/ha_maria.cc 2012-08-14 16:59:28 +0000 > +++ b/storage/maria/ha_maria.cc 2012-09-27 21:29:26 +0000 > @@ -1633,6 +1633,9 @@ please, use the recipe from https://kb.askmonty.org/en/how-to-get-more-out-of-bzr-when-working-on-maria… it's much more difficult to review patches without sufficient context (that is, without function names on every chunk) > param->testflag|= T_REP_BY_SORT; > error= maria_repair_by_sort(param, file, fixed_name, > test(param->testflag & T_QUICK)); > + if (error && share->state.dupp_key != MARIA_MAX_KEY) > + print_keydup_error(share->state.dupp_key, > + ER(ER_DUP_ENTRY_WITH_KEY_NAME)); > } > } > else > > === modified file 'storage/myisam/ha_myisam.cc' > --- a/storage/myisam/ha_myisam.cc 2012-07-18 18:40:15 +0000 > +++ b/storage/myisam/ha_myisam.cc 2012-09-27 21:29:26 +0000 > @@ -1130,6 +1130,9 @@ > thd_proc_info(thd, "Repair by sorting"); > error = mi_repair_by_sort(&param, file, fixed_name, > test(param.testflag & T_QUICK)); > + if (error && share->state.dupp_key != MAX_KEY) > + print_keydup_error(share->state.dupp_key, > + ER(ER_DUP_ENTRY_WITH_KEY_NAME)); I don't understand it. print_keydup_error() takes the value to print from table->record[[0]. But - in the case of mi_repair_by_sort - how does the value end up here? What code puts the conflicting key value into table->record[0] ? > } > } > else > > === modified file 'storage/myisam/mi_check.c' > --- a/storage/myisam/mi_check.c 2012-04-07 13:58:46 +0000 > +++ b/storage/myisam/mi_check.c 2012-09-27 21:29:26 +0000 > @@ -2408,12 +2411,22 @@ > (my_bool) (!(param->testflag & T_VERBOSE)), > param->sort_buffer_length)) > { > + > + if (sort_param.sort_info->dupp) > + { > + share->state.dupp_key= sort_param.key; > + keydup_error= 1; > + } > + if (!(keydup_error && param->suppress_keydup_error_handling)) 1. please use param->testflag for flags, instead of a new special field 2. I don't see why are you doing it here, normally the error is issued below, as "Duplicate key for record at %10s against...." I'd expect to see any new code that deals with duplicate key errors at the same place where the old code is > + { > param->retry_repair= 1; > if (! param->error_printed) > mi_check_print_error(param, "Couldn't fix table with create_index_by_sort(). Error: %d", > my_errno); > + } > goto err; > } > + > /* No need to calculate checksum again. */ > sort_param.calc_checksum= 0; > free_root(&sort_param.wordroot, MYF(0)); > @@ -2533,18 +2546,27 @@ > } > if (got_error) > { > + if (!(keydup_error && param->suppress_keydup_error_handling)) > + { > if (! param->error_printed) > mi_check_print_error(param,"%d when fixing table",my_errno); You could simply set param->error_printed when you get dplicate key error - it'll avoid all further errors, you won't need to wrap them all in if()'s > + } > if (new_file >= 0) > { > (void) mysql_file_close(new_file, MYF(0)); > (void) mysql_file_delete(mi_key_file_datatmp, > param->temp_filename, MYF(MY_WME)); > + if (!(keydup_error && param->suppress_keydup_error_handling)) > + { Why do you need this if() ? > if (info->dfile == new_file) /* Retry with key cache */ > if (unlikely(mi_open_datafile(info, share, name, -1))) > param->retry_repair= 0; /* Safety */ > + } > } > + if (!(keydup_error && param->suppress_keydup_error_handling)) > + { > mi_mark_crashed_on_repair(info); > + } Why do you need to avoid mi_mark_crashed_on_repair() ? > if (killed_ptr(param)) > param->retry_repair= 0; /* No use to retry repair */ > } > @@ -4682,8 +4708,9 @@ > (!rows || rows >= MI_MIN_ROWS_TO_DISABLE_INDEXES)); > for (i=0 ; i < share->base.keys ; i++,key++) > { > - if (!(key->flag & (HA_NOSAME | HA_SPATIAL | HA_AUTO_KEY)) && > - ! mi_too_big_key_for_sort(key,rows) && info->s->base.auto_key != i+1) > + if (!(key->flag & (HA_SPATIAL | HA_AUTO_KEY)) && > + ! mi_too_big_key_for_sort(key,rows) && info->s->base.auto_key != i+1 && > + (!(key->flag & HA_NOSAME) || info->create_unique_index_by_sort)) Now, this directly contradicts the function name and the function comment. Could you please rename the function and update the comment? And instead of checking info->create_unique_index_by_sort I'd rather pass it down as an argument: void mi_disable_indexes(MI_INFO *info, ha_rows rows, my_bool only_non_unique) { > { > mi_clear_key_active(share->state.key_map, i); > info->update|= HA_STATE_CHANGED; > > === modified file 'storage/myisam/mi_extra.c' > --- a/storage/myisam/mi_extra.c 2012-07-31 17:29:07 +0000 > +++ b/storage/myisam/mi_extra.c 2012-09-27 21:29:26 +0000 > @@ -365,6 +365,9 @@ > case HA_EXTRA_CHANGE_KEY_TO_DUP: > mi_extra_keyflag(info, function); > break; > + case HA_EXTRA_CREATE_UNIQUE_INDEX_BY_SORT: > + info->create_unique_index_by_sort= 1; > + break; Two comments here: 1. I don't like the name "create unique index by sort", because ALTER TABLE itself does not create any indexes by sort or anything it simply tells the engine to optimize a bulk insertion. and it's myisam that does repair-by-sort. I mean, a better name would be a high-level name that does not imply myisam-specific details. something like HA_EXTRA_BULK_INSERT_DUPLICATES_UNLIKELY which tells the engine to optimize the following bulk insert for the case when unique key conflicts are unlikely to happen. (in myisam case it means to build unique indexes by sort, right) 2. I've discussed this with Monty and we both didn't like using extra() here, even to preserve the API compatibility. Monty thinks there's no need to bother, and we can change start_bulk_insert() any way we want. But to make it better for third-party engines, I'd suggest something like class handler { ... virtual void start_bulk_insert(ha_rows rows) {} virtual void start_bulk_insert(ha_rows rows, ulonglong flags) { start_bulk_insert(rows); } ... } that is, you introduce the new function, and keep the old one as a fallback. This way it stays compatible, and old engines won't need any changes. But myisam/maria will implement the new start_bulk_insert variant, with two arguments, and will recognize the flag HA_BULK_INSERT_DUPLICATES_UNLIKELY And no extra() is needed. > case HA_EXTRA_MMAP: > #ifdef HAVE_MMAP > mysql_mutex_lock(&share->intern_lock); > Regards, Sergei

1 0

[Maria-developers] MariaDB 5.3.10 release prep
by Daniel Bartholomew 12 Nov '12

12 Nov '12

Preliminary release notes and changelog pages have been created for 5.3.10. https://kb.askmonty.org/en/mariadb-5310-release-notes/ https://kb.askmonty.org/en/mariadb-5310-changelog/ Please let me know if there is anything in particular which should be mentioned in the release notes. Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

1 0

[Maria-developers] MariaDB 10.0.0 Alpha Release Prep
by Daniel Bartholomew 12 Nov '12

12 Nov '12

All, The files for this release have been uploaded to the mirrors and we're just waiting until Monday morning, U.S. Eastern time, to announce the release. The preliminary release notes and changelog pages are here: - https://kb.askmonty.org/en/mariadb-1000-release-notes/ - https://kb.askmonty.org/en/mariadb-1000-changelog/ The release notes page is pretty sparse. Its content is from the list of "New already done Features" on the "What is MariaDB 10.0?" page. Please send me additions and corrections ASAP (or make the changes yourself). Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

1 0

Re: [Maria-developers] [Commits] Rev 3569: MDEV-3830 - fix build on Intel compiler in file:///H:/bzr/5.5/
by Kristian Nielsen 04 Nov '12

04 Nov '12

Vladislav Vaintroub <wlad(a)montyprogram.com> writes: > === modified file 'mysys/my_context.c' > --- a/mysys/my_context.c 2012-10-31 11:47:25 +0000 > +++ b/mysys/my_context.c 2012-11-02 23:31:50 +0000 > @@ -206,7 +206,7 @@ > ( > "movq %%rsp, (%[save])\n\t" > "movq %[stack], %%rsp\n\t" > -#if __GNUC__ >= 4 && __GNUC_MINOR__ >= 4 > +#if __GNUC__ >= 4 && __GNUC_MINOR__ >= 4 && !defined(__INTEL_COMPILER) There are two places this is done (one for 32-bit and one for 64-bit). You probably need the !defined(__INTEL_COMPILER) on both. - Kristian.

1 0

Re: [Maria-developers] [Commits] Rev 3589: Fixed bug mdev-585 (LP bug #637962) in file:///home/igor/maria/maria-5.3-mdev585/
by Sergei Petrunia 02 Nov '12

02 Nov '12

Hello Igor, The fix seems to be ok to push. Which version do you want to push it into? This is not a crash, should we really push it into 5.3, or to 5.5? On Thu, Nov 01, 2012 at 02:54:34PM -0700, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-mdev585/ > > ------------------------------------------------------------ > revno: 3589 > revision-id: igor(a)askmonty.org-20121101215433-ctfm3zfhc6uhuy3i > parent: psergey(a)askmonty.org-20121031053425-kfn0l7yj6z7jtnqw > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-mdev585 > timestamp: Thu 2012-11-01 14:54:33 -0700 > message: > Fixed bug mdev-585 (LP bug #637962) > If, when executing a query with ORDER BY col LIMIT n, the optimizer chose > an index-merge scan to access the table containing col while there existed > an index defined over col then optimizer did not consider the possibility > of using an alternative range scan by this index to avoid filesort. This > could cause a performance degradation if the optimizer flag index_merge was > set up to 'on'. > === modified file 'mysql-test/r/range_vs_index_merge.result' > --- a/mysql-test/r/range_vs_index_merge.result 2012-03-21 00:03:28 +0000 > +++ b/mysql-test/r/range_vs_index_merge.result 2012-11-01 21:54:33 +0000 > @@ -1221,6 +1221,150 @@ > Seattle USA 563374 > Caracas VEN 1975294 > set optimizer_switch=@save_optimizer_switch; > +# > +# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n > +# (LP bug #637962) > +# > +DROP INDEX CountryPopulation ON City; > +DROP INDEX CountryName ON City; > +DROP INDEX CityName on City; > +CREATE INDEX Name ON City(Name); > +CREATE INDEX Population ON City(Population); > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City index_merge Country,Name,Population Name,Country 35,3 NULL # Using sort_union(Name,Country); Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000); > +ID Name Country Population > +384 Cabo Frio BRA 119503 > +387 Camaragibe BRA 118968 > +403 Catanduva BRA 107761 > +412 Cachoeirinha BRA 103240 > +508 Watford GBR 113080 > +509 Ipswich GBR 114000 > +510 Slough GBR 112000 > +511 Exeter GBR 111000 > +512 Cheltenham GBR 106000 > +513 Gloucester GBR 107000 > +514 Saint Helens GBR 106293 > +515 Sutton Coldfield GBR 106001 > +516 York GBR 104425 > +517 Oldham GBR 103931 > +518 Basildon GBR 100924 > +519 Worthing GBR 100000 > +635 Mallawi EGY 119283 > +636 Bilbays EGY 113608 > +637 Mit Ghamr EGY 101801 > +638 al-Arish EGY 100447 > +701 Tarragona ESP 113016 > +702 Lleida (Lérida) ESP 112207 > +703 Jaén ESP 109247 > +704 Ourense (Orense) ESP 109120 > +705 Mataró ESP 104095 > +706 Algeciras ESP 103106 > +707 Marbella ESP 101144 > +759 Gonder ETH 112249 > +869 Cabuyao PHL 106630 > +870 Calapan PHL 105910 > +873 Cauayan PHL 103952 > +903 Serekunda GMB 102600 > +909 Sohumi GEO 111700 > +913 Tema GHA 109975 > +914 Sekondi-Takoradi GHA 103653 > +924 Villa Nueva GTM 101295 > +1844 Cape Breton CAN 114733 > +1847 Cambridge CAN 109186 > +2406 Herakleion GRC 116178 > +2407 Kallithea GRC 114233 > +2408 Larisa GRC 113090 > +2908 Cajamarca PER 108009 > +3002 Besançon FRA 117733 > +3003 Caen FRA 113987 > +3004 Orléans FRA 113126 > +3005 Mulhouse FRA 110359 > +3006 Rouen FRA 106592 > +3007 Boulogne-Billancourt FRA 106367 > +3008 Perpignan FRA 105115 > +3009 Nancy FRA 103605 > +3411 Ceyhan TUR 102412 > +3567 Carúpano VEN 119639 > +3568 Catia La Mar VEN 117012 > +3571 Calabozo VEN 107146 > +3786 Cam Ranh VNM 114041 > +3792 Tartu EST 101246 > +4002 Carrollton USA 109576 > +4027 Cape Coral USA 102286 > +4032 Cambridge USA 101355 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 2 > +Handler_read_next 385 > +Handler_read_prev 0 > +Handler_read_rnd 377 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City range Country,Name,Population Population 4 NULL # Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +ID Name Country Population > +519 Worthing GBR 100000 > +638 al-Arish EGY 100447 > +518 Basildon GBR 100924 > +707 Marbella ESP 101144 > +3792 Tartu EST 101246 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 1 > +Handler_read_next 59 > +Handler_read_prev 0 > +Handler_read_rnd 0 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +set optimizer_switch='index_merge=off'; > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City range Country,Name,Population Population 4 NULL # Using index condition; Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +ID Name Country Population > +519 Worthing GBR 100000 > +638 al-Arish EGY 100447 > +518 Basildon GBR 100924 > +707 Marbella ESP 101144 > +3792 Tartu EST 101246 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 1 > +Handler_read_next 59 > +Handler_read_prev 0 > +Handler_read_rnd 0 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +set optimizer_switch=@save_optimizer_switch; > DROP DATABASE world; > use test; > CREATE TABLE t1 ( > > === modified file 'mysql-test/r/range_vs_index_merge_innodb.result' > --- a/mysql-test/r/range_vs_index_merge_innodb.result 2012-03-21 00:03:28 +0000 > +++ b/mysql-test/r/range_vs_index_merge_innodb.result 2012-11-01 21:54:33 +0000 > @@ -1222,6 +1222,150 @@ > Seattle USA 563374 > Caracas VEN 1975294 > set optimizer_switch=@save_optimizer_switch; > +# > +# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n > +# (LP bug #637962) > +# > +DROP INDEX CountryPopulation ON City; > +DROP INDEX CountryName ON City; > +DROP INDEX CityName on City; > +CREATE INDEX Name ON City(Name); > +CREATE INDEX Population ON City(Population); > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City index_merge Country,Name,Population Name,Country 35,3 NULL # Using sort_union(Name,Country); Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000); > +ID Name Country Population > +384 Cabo Frio BRA 119503 > +387 Camaragibe BRA 118968 > +403 Catanduva BRA 107761 > +412 Cachoeirinha BRA 103240 > +508 Watford GBR 113080 > +509 Ipswich GBR 114000 > +510 Slough GBR 112000 > +511 Exeter GBR 111000 > +512 Cheltenham GBR 106000 > +513 Gloucester GBR 107000 > +514 Saint Helens GBR 106293 > +515 Sutton Coldfield GBR 106001 > +516 York GBR 104425 > +517 Oldham GBR 103931 > +518 Basildon GBR 100924 > +519 Worthing GBR 100000 > +635 Mallawi EGY 119283 > +636 Bilbays EGY 113608 > +637 Mit Ghamr EGY 101801 > +638 al-Arish EGY 100447 > +701 Tarragona ESP 113016 > +702 Lleida (Lérida) ESP 112207 > +703 Jaén ESP 109247 > +704 Ourense (Orense) ESP 109120 > +705 Mataró ESP 104095 > +706 Algeciras ESP 103106 > +707 Marbella ESP 101144 > +759 Gonder ETH 112249 > +869 Cabuyao PHL 106630 > +870 Calapan PHL 105910 > +873 Cauayan PHL 103952 > +903 Serekunda GMB 102600 > +909 Sohumi GEO 111700 > +913 Tema GHA 109975 > +914 Sekondi-Takoradi GHA 103653 > +924 Villa Nueva GTM 101295 > +1844 Cape Breton CAN 114733 > +1847 Cambridge CAN 109186 > +2406 Herakleion GRC 116178 > +2407 Kallithea GRC 114233 > +2408 Larisa GRC 113090 > +2908 Cajamarca PER 108009 > +3002 Besançon FRA 117733 > +3003 Caen FRA 113987 > +3004 Orléans FRA 113126 > +3005 Mulhouse FRA 110359 > +3006 Rouen FRA 106592 > +3007 Boulogne-Billancourt FRA 106367 > +3008 Perpignan FRA 105115 > +3009 Nancy FRA 103605 > +3411 Ceyhan TUR 102412 > +3567 Carúpano VEN 119639 > +3568 Catia La Mar VEN 117012 > +3571 Calabozo VEN 107146 > +3786 Cam Ranh VNM 114041 > +3792 Tartu EST 101246 > +4002 Carrollton USA 109576 > +4027 Cape Coral USA 102286 > +4032 Cambridge USA 101355 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 2 > +Handler_read_next 385 > +Handler_read_prev 0 > +Handler_read_rnd 377 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City range Country,Name,Population Population 4 NULL # Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +ID Name Country Population > +519 Worthing GBR 100000 > +638 al-Arish EGY 100447 > +518 Basildon GBR 100924 > +707 Marbella ESP 101144 > +3792 Tartu EST 101246 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 1 > +Handler_read_next 59 > +Handler_read_prev 0 > +Handler_read_rnd 0 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +set optimizer_switch='index_merge=off'; > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City range Country,Name,Population Population 4 NULL # Using index condition; Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +ID Name Country Population > +519 Worthing GBR 100000 > +638 al-Arish EGY 100447 > +518 Basildon GBR 100924 > +707 Marbella ESP 101144 > +3792 Tartu EST 101246 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 1 > +Handler_read_next 59 > +Handler_read_prev 0 > +Handler_read_rnd 0 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +set optimizer_switch=@save_optimizer_switch; > DROP DATABASE world; > use test; > CREATE TABLE t1 ( > > === modified file 'mysql-test/t/range_vs_index_merge.test' > --- a/mysql-test/t/range_vs_index_merge.test 2012-03-21 00:03:28 +0000 > +++ b/mysql-test/t/range_vs_index_merge.test 2012-11-01 21:54:33 +0000 > @@ -675,6 +675,64 @@ > $cond; > > set optimizer_switch=@save_optimizer_switch; > + > +--echo # > +--echo # Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n > +--echo # (LP bug #637962) > +--echo # > + > +DROP INDEX CountryPopulation ON City; > +DROP INDEX CountryName ON City; > +DROP INDEX CityName on City; > + > +CREATE INDEX Name ON City(Name); > +CREATE INDEX Population ON City(Population); > + > + > +--replace_column 9 # > +EXPLAIN > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000); > +FLUSH STATUS; > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000); > +SHOW STATUS LIKE 'Handler_read_%'; > + > + > +--replace_column 9 # > +EXPLAIN > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > + > +FLUSH STATUS; > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +SHOW STATUS LIKE 'Handler_read_%'; > + > + > +set optimizer_switch='index_merge=off'; > + > +--replace_column 9 # > +EXPLAIN > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > + > +FLUSH STATUS; > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +SHOW STATUS LIKE 'Handler_read_%'; > + > +set optimizer_switch=@save_optimizer_switch; > > > DROP DATABASE world; > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2012-10-10 05:21:22 +0000 > +++ b/sql/sql_select.cc 2012-11-01 21:54:33 +0000 > @@ -18079,15 +18079,18 @@ > */ > > if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE || > - quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || > + quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || > quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || > quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) > - goto use_filesort; > - ref_key= select->quick->index; > - ref_key_parts= select->quick->used_key_parts; > + ref_key= MAX_KEY; > + else > + { > + ref_key= select->quick->index; > + ref_key_parts= select->quick->used_key_parts; > + } > } > > - if (ref_key >= 0) > + if (ref_key >= 0 && ref_key != MAX_KEY) > { > /* > We come here when there is a REF key. > @@ -18229,7 +18232,8 @@ > else > keys= usable_keys; > > - if (ref_key >= 0 && table->covering_keys.is_set(ref_key)) > + if (ref_key >= 0 && ref_key != MAX_KEY && > + table->covering_keys.is_set(ref_key)) > ref_key_quick_rows= table->quick_rows[ref_key]; > > read_time= join->best_positions[tablenr].read_time; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1