From: Ole John Aske Date: October 20 2010 1:53pm Subject: bzr commit into mysql-5.1-telco-7.0-spj branch (ole.john.aske:3227) Bug#57601 List-Archive: http://lists.mysql.com/commits/121334 X-Bug: 57601 Message-Id: <20101020135307.A661021D@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============6613656987320784829==" --===============6613656987320784829== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj/ based on revid:jonas@stripped 3227 Ole John Aske 2010-10-20 Cherry picked proposed fix for bug#57601 into telco.7-0.spj bran ch: Make the optimizer less eager in requesting ordered access when it is actually not needed. modified: mysql-test/suite/ndb/r/ndb_join_pushdown.result sql/opt_range.cc sql/opt_range.h sql/sql_select.cc === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-07-13 11:23:52 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-10-20 13:53:02 +0000 @@ -3138,7 +3138,7 @@ scan_count pruned_scan_count 1 sorted_scan_count -2 +1 pushed_queries_defined 3 pushed_queries_dropped === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2010-10-06 10:06:47 +0000 +++ b/sql/opt_range.cc 2010-10-20 13:53:02 +0000 @@ -4983,7 +4983,7 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_ { if (!(quick= get_quick_select(param, (*first_scan)->idx, (*first_scan)->sel_arg, alloc)) || - quick_intrsect->push_quick_back(quick)) + (quick->sorted= 1, quick_intrsect->push_quick_back(quick))) { delete quick_intrsect; DBUG_RETURN(NULL); @@ -4998,6 +4998,7 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_ DBUG_RETURN(NULL); } quick->file= NULL; + quick->sorted= 1; quick_intrsect->cpk_quick= quick; } quick_intrsect->records= records; @@ -5024,7 +5025,7 @@ QUICK_SELECT_I *TRP_ROR_UNION::make_quic for (scan= first_ror; scan != last_ror; scan++) { if (!(quick= (*scan)->make_quick(param, FALSE, &quick_roru->alloc)) || - quick_roru->push_quick_back(quick)) + (quick->sorted= 1, quick_roru->push_quick_back(quick))) DBUG_RETURN(NULL); } quick_roru->records= records; @@ -8454,7 +8455,7 @@ int QUICK_RANGE_SELECT::reset() in_range= FALSE; cur_range= (QUICK_RANGE**) ranges.buffer; - if (file->inited == handler::NONE && (error= file->ha_index_init(index,1))) + if (file->inited == handler::NONE && (error= file->ha_index_init(index,sorted))) DBUG_RETURN(error); /* Do not allocate the buffers twice. */ @@ -8671,7 +8672,7 @@ int QUICK_RANGE_SELECT::get_next_prefix( result= file->read_range_first(last_range->min_keypart_map ? &start_key : 0, last_range->max_keypart_map ? &end_key : 0, test(last_range->flag & EQ_RANGE), - TRUE); + sorted); if (last_range->flag == (UNIQUE_RANGE | EQ_RANGE)) last_range= 0; // Stop searching @@ -8797,6 +8798,7 @@ QUICK_SELECT_DESC::QUICK_SELECT_DESC(QUI r->flag&= ~EQ_RANGE; } rev_it.rewind(); + sorted= 1; q->dont_free=1; // Don't free shared mem delete q; } @@ -10208,11 +10210,18 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *par if (quick_prefix_records == HA_POS_ERROR) quick->quick_prefix_select= NULL; /* Can't construct a quick select. */ else + { /* Make a QUICK_RANGE_SELECT to be used for group prefix retrieval. */ quick->quick_prefix_select= get_quick_select(param, param_idx, index_tree, &quick->alloc); - + if (!quick->quick_prefix_select) + { + delete quick; + DBUG_RETURN(NULL); + } + quick->quick_prefix_select->sorted= 1; + } /* Extract the SEL_ARG subtree that contains only ranges for the MIN/MAX attribute, and create an array of QUICK_RANGES to be used by the @@ -10603,6 +10612,9 @@ int QUICK_GROUP_MIN_MAX_SELECT::reset(vo DBUG_ENTER("QUICK_GROUP_MIN_MAX_SELECT::reset"); head->set_keyread(TRUE); /* We need only the key attributes */ + + // Request ordered index access as usage of ::index_last(), ::index_first() + // within GROUP_MIN_MAX depends on it. if ((result= file->ha_index_init(index,1))) DBUG_RETURN(result); if (quick_prefix_select && quick_prefix_select->reset()) === modified file 'sql/opt_range.h' --- a/sql/opt_range.h 2010-10-06 10:06:47 +0000 +++ b/sql/opt_range.h 2010-10-20 13:53:02 +0000 @@ -350,7 +350,7 @@ class SEL_ARG; /* Quick select that does a range scan on a single key. The records are - returned in key order. + returned in key order if 'QUICK_SELECT_I::sorted==true'. */ class QUICK_RANGE_SELECT : public QUICK_SELECT_I { === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-10-15 18:56:29 +0000 +++ b/sql/sql_select.cc 2010-10-20 13:53:02 +0000 @@ -1821,6 +1821,17 @@ JOIN::save_join_tab() } +static void +disable_sorted_access(JOIN_TAB* join_tab) +{ + join_tab->sorted= 0; + if (join_tab->select && join_tab->select->quick) + { + join_tab->select->quick->sorted= 0; + } +} + + /** Exec select. @@ -1990,7 +2001,7 @@ JOIN::exec() DBUG_PRINT("info", ("%s", thd->proc_info)); if (!curr_join->sort_and_group && curr_join->const_tables != curr_join->tables) - curr_join->join_tab[curr_join->const_tables].sorted= 0; + disable_sorted_access(&curr_join->join_tab[curr_join->const_tables]); if ((tmp_error= do_select(curr_join, (List *) 0, curr_tmp_table, 0))) { error= tmp_error; @@ -2154,7 +2165,7 @@ JOIN::exec() curr_join->group_list= 0; if (!curr_join->sort_and_group && curr_join->const_tables != curr_join->tables) - curr_join->join_tab[curr_join->const_tables].sorted= 0; + disable_sorted_access(&curr_join->join_tab[curr_join->const_tables]); if (setup_sum_funcs(curr_join->thd, curr_join->sum_funcs) || (tmp_error= do_select(curr_join, (List *) 0, curr_tmp_table, 0))) @@ -6738,7 +6749,7 @@ make_join_readinfo(JOIN *join, ulonglong uint i; bool statistics= test(!(join->select_options & SELECT_DESCRIBE)); bool ordered_set= 0; - bool sorted= 1; + bool sorted= (join->order || join->group_list); DBUG_ENTER("make_join_readinfo"); for (i=join->const_tables ; i < join->tables ; i++) @@ -12036,7 +12047,7 @@ join_read_key(JOIN_TAB *tab) if (!table->file->inited) { - table->file->ha_index_init(tab->ref.key, tab->sorted); + table->file->ha_index_init(tab->ref.key, 0); } if (cmp_buffer_with_ref(tab) || (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) @@ -12365,7 +12376,7 @@ join_read_last(JOIN_TAB *tab) tab->read_record.index=tab->index; tab->read_record.record=table->record[0]; if (!table->file->inited) - table->file->ha_index_init(tab->index, 1); + table->file->ha_index_init(tab->index, tab->sorted); if ((error= tab->table->file->index_last(tab->table->record[0]))) return report_error(table, error); return 0; @@ -12389,7 +12400,7 @@ join_ft_read_first(JOIN_TAB *tab) TABLE *table= tab->table; if (!table->file->inited) - table->file->ha_index_init(tab->ref.key, 1); + table->file->ha_index_init(tab->ref.key, tab->sorted); #if NOT_USED_YET /* as ft-key doesn't use store_key's, see also FT_SELECT::init() */ if (cp_buffer_from_ref(tab->join->thd, table, &tab->ref)) --===============6613656987320784829== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/ole.john.aske@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: ole.john.aske@stripped\ # 6x91z3q0mggaiv2f # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj/ # testament_sha1: 5813e4cbf77c2b68ee172a65c149438f8947ee09 # timestamp: 2010-10-20 15:53:07 +0200 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0/ # base_revision_id: jonas@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWVd7PEsABJFfgFUweff//36/ /kq////+YAkO+nnN72SrUNBn3t4FPQBoFTsISkampPKbKbU9T0nkT0mmIZADRoAAADRo9QEpGiZN AmaCnqnqYmm1NAAAAAAAABqp6ZT1PIjQBo0AAAAAAAAAAEiQEJoFT8FEbUD9JtJqPUAaAGgAANAp 6qGhtTRoDQ0AAA0aGQ0NAAAPUASSE0BGTTTJNoTEaNT1NqYmSPUmmBGRoAM0edUQBqNJWH9NPRYo u/uVZuIVWuneYxo1p69pUx09kGZFFYMXLONEyklsnBOcEpQatUHrYc6W6/HkgX6tL7XjYvnspwjT F/KV0JoZYvoi+DSxkony26kgUiAxZJzLy/n0Tm8PYixzD7g2A0m022NoPJysfkIMq4zIW1ukcX6D 40e+/AdDi8VjKWHLK2VsbTZZl8Z0nOjgpAElWKdBSpIDkSRUq7zey7Clb4aYGOfncZhIDZHWg1Qq DhQkBlcfrcdBkRwGyy7kinUxzgGcHfhKp2gJ3IYVTAA8N1k2VCzFZt27k0Em3LEfN4pZeuhC3M1L aI5iIFFolJSZpIRjPBRWJcMQqbSMfe1s9yKXXSNMY+3w6tCCgwKyeQZ4jxDN5/SQeNeq2C6+womw 4OFt87OJQBhHVE4kKFXO3iCerkUy5I+V+W6OE7URJ0sGDcRWIxERZB6JeRvFCFkQeqvHPKItZIrY VLJPe72b7ZIEwqjNcIa9enGZNIJCgSGOsgUBmTO4FIFdqg6ozgUsM76TKimVoNli/9yvWDJKqVll MRxhF239QXN5aap/locgKojih7M5nQeX5/G4iaBkC9mAJQYTGstqPGoyRwMDCUyz0SEpIyKTQ/Dq 1RLcbJW6QUBRLT4RSglCwXn0+4sdJi4qPRhDV5XSq9yl7LFeKESWkesway7MFZErNMBVGBmQspcj Zwprk4iAXVbaXaQTBbDTDvCqmDsTDnUXK4kLwjoMLynbG1ZFDC7LZZVnBEuiV7ZC0zKzVIwfZ4Cq z3a9sG/bi8qQCtIU4Td6RJOzIBcxb4t343SpB9LISvGpAhoWVzem8n+BKtTWu2uq5r4bKGzGhqxB zEfywbQQgMmUuHo8XgSATVHmJcg4uJPTAqToHGmO8PsOMpjhLCajbKmtvGbGjBkBckxFjSzAg0RL cZow1swvqfDJbtfU25j3l4ijoFJRgXv9xn5mk6zQY8I2anUXjW2qQtkUGlzM8gQChsMApKtWMlbV wMbroI80Nhdom0Eaj6pYLBivt3YlL4iUkZxYo9FIbqreRMsIBjVW0x2mFEdxRXJtqZ2iGRmOONHF ZqM3QCWldd2CtzdRSo8oMVboUUu2FqjyOw1XFj6IzqJGOReoWHgNUm9187ahHOB8zaX9QEnRDKWt OKMEkTIOEjFFtdiMJyNBplCNYa3nxlteNvjdwdDDWmOSNjHWQnd0RXQlfB96LMY+255EafSA1/Bz 3GLbZSPZCIGk7RXp3h1nNgBQ6K5SlP2NxgoAgAIAQDm3NCu9p3iOdL39CZ20PCMB49JCMimaMyR1 HQdWW3JWUirYaataefcLIrqYqVtJgOG1qXwamIHgp2hpVEp/NFC698rgLXKlz4VQC6d0meWwjyn1 +8OPkZmXxIh6nymoSViY+g4HBexfeYCUmk9r3VFB1H1ESc9CspSo/AF5/X+H37QcaJjSwrbkj2NE y+5Jb4KfKe+BOIwDEw1jHDqUCczjiY3BtJj6/OowPsKiYu/EpA+ZhOmoxHfUkf4YSfyFknOByUGO 0pNor9EyJbmR7nmN1dcEwggOEtZSCM/+j8zuQR+Z/YogkXg1NXuzyGgiaDibKOhcKNIqykpOuMQJ kD3jVphQ49hR+b0+0uy6CiP8rp6FLdy3m+b1/jY+FqPfP0h2ZiIEUwkyZIM5yNWElgVGaaND9XUU kxsJx5xJTuY8Q6wtSUsrZ2TqwsSg5XbHG5hOzXh2rU8M8CxQSzYHMMt+rSTPhYWIrTooUMLK+Fmv I4sM4GS/sqR8Ewofc7kPx2OGoxeU/jVVIcdjmDr4UOdlkbUpByVSByDUpdhQHFyNorxwbjLiOO0x 7CVehxd5eHcUnwGyTnqSGVKmSsmMyNpw1r9g58Vv5g8PRtBaub1oHdm3edwK8nMJRMg3AnqAV7V5 rXtKjUP7xmEShmcuy3p9r+IA80iQ9Fl6v5kHPQXslrn6YoGlr8mPcjcakBIoeDE4njCNKT791+QG umeEkYv3yy7+pLJNWq0vB+gG5F79W+U4AqFSEKOo1jeC7V2JLQWMXsnOb0bSYKzghXMlnM2dkk+1 T5DrzFaUSU8DYcjr9UGARS0uB6n1jyJ9kwdE0aBKaSgNjCaqBaDZLDb6m+B56bj2maQY1O0nFbyk H2HxBWu3LTPzUymRQqOj/mjXGnk2mlwDPSV/WoEBMI1A5dJoJf18zY9BCnNPPISCyORDSor10G+c oRMyYYTxwRw8N5xxUmT+fNJSMAVdxjeXoYHdGqQiitKdaYHblVGAzE1vjJtErkpObEVeAcZYp3TR DSdyaUR38Kb2BhWqKNdHJR5UHE2FktVC7TJRIlcQVhyBZ13mVCqXYWk/Q+fk947LkHMdTDTC7XVq WzIlLwA0I5FRQHeUMjUYpGiRrujkeBNRQrlhoTtkmQsd4ShOR76AB9g4g0zGqPAisQ11HWTWBhZY 2njjvzYw3WGc2pBJYJQHm0JLiZmth1bj+MzkNDeeS7A7m23lrDoKUEYygSxyK8MoZCiQtwLO+/NP fYKDmsCqrUtmEUfcG0UAJVk7UywGXxTECyNBaAXTY2cB4Dmj9CsztPcZnGW1kmR/d7hT6M0Hm9Wc T0OEfHiY0JwBlw+Di5qqwvzo71nM3srgVBvrIp3kPSvNclXvlMTyIeo15Uqj3lnsV2MbgTCcXHmc CCDcbt0IOe4cSGYvEddEXPgOMExTWCMRRkVcnM4v2MOxkVrzy/4u5IpwoSCu9niW --===============6613656987320784829==--