From: Oystein Grovlen Date: September 17 2010 12:34pm Subject: bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3244) WL#5559 List-Archive: http://lists.mysql.com/commits/118474 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0185199794==" --===============0185199794== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/og136792/mysql/mysql-next-mr-wl5559/ based on revid:jorgen.loland@stripped 3244 Oystein Grovlen 2010-09-17 WL#5559 - Factor tmp table out of optimizer. Phase 1. Moved code to create temporary table from JOIN::optimize() to a new method JOIN::create_tmp_table_for_sort(). Moved the call of this method to JOIN::exec(). Some clean-up at end of JOIN::optimize that is possible when this code has been moved. @ sql/sql_select.cc - Added method JOIN::create_tmp_table_for_sort() with code moved from JOIN::optimize(). This method is now called from JOIN::exec. - Also moved code for setting tmp_table_param.precomputed_group_by from JOIN::optimize() to JOIN::exec() - Dropped local variable in JOIN::optimize(), need_distinct. JOIN::create_tmp_table_for_sort() instead calls is_using_agg_loose_index_scan() the same way as JOIN:exec() currently does. - Clean-up of code for returning from JOIN::optimize(). @ sql/sql_select.h Added method JOIN::create_tmp_table_for_sort(). modified: sql/sql_select.cc sql/sql_select.h === modified file 'sql/sql_select.cc' === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-09-15 08:53:08 +0000 +++ b/sql/sql_select.cc 2010-09-17 12:34:23 +0000 @@ -1752,7 +1752,6 @@ int JOIN::optimize() { - bool need_distinct; ulonglong select_opts_for_readinfo; uint no_jbuf_after; @@ -2458,148 +2457,9 @@ } tmp_having= having; - if (select_options & SELECT_DESCRIBE) - { - error= 0; - DBUG_RETURN(0); - } - having= 0; - - /* - The loose index scan access method guarantees that all grouping or - duplicate row elimination (for distinct) is already performed - during data retrieval, and that all MIN/MAX functions are already - computed for each group. Thus all MIN/MAX functions should be - treated as regular functions, and there is no need to perform - grouping in the main execution loop. - Notice that currently loose index scan is applicable only for - single table queries, thus it is sufficient to test only the first - join_tab element of the plan for its access method. - */ - need_distinct= TRUE; - if (join_tab->is_using_loose_index_scan()) - { - tmp_table_param.precomputed_group_by= TRUE; - if (join_tab->is_using_agg_loose_index_scan()) - { - need_distinct= FALSE; - tmp_table_param.precomputed_group_by= FALSE; - } - } - - /* Create a tmp table if distinct or if the sort is too complicated */ - if (need_tmp) - { - DBUG_PRINT("info",("Creating tmp table")); - thd_proc_info(thd, "Creating tmp table"); - - init_items_ref_array(); - - tmp_table_param.hidden_field_count= (all_fields.elements - - fields_list.elements); - ORDER *tmp_group= ((!simple_group && !procedure && - !(test_flags & TEST_NO_KEY_GROUP)) ? group_list : - (ORDER*) 0); - /* - Pushing LIMIT to the temporary table creation is not applicable - when there is ORDER BY or GROUP BY or there is no GROUP BY, but - there are aggregate functions, because in all these cases we need - all result rows. - */ - ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order) && - !tmp_group && - !thd->lex->current_select->with_sum_func) ? - select_limit : HA_POS_ERROR; - - if (!(exec_tmp_table1= - create_tmp_table(thd, &tmp_table_param, all_fields, - tmp_group, group_list ? 0 : select_distinct, - group_list && simple_group, - select_options, tmp_rows_limit, ""))) - DBUG_RETURN(1); - - /* - We don't have to store rows in temp table that doesn't match HAVING if: - - we are sorting the table and writing complete group rows to the - temp table. - - We are using DISTINCT without resolving the distinct as a GROUP BY - on all columns. - - If having is not handled here, it will be checked before the row - is sent to the client. - */ - if (tmp_having && - (sort_and_group || (exec_tmp_table1->distinct && !group_list))) - having= tmp_having; - - /* if group or order on first table, sort first */ - if (group_list && simple_group) - { - DBUG_PRINT("info",("Sorting for group")); - thd_proc_info(thd, "Sorting for group"); - if (create_sort_index(thd, this, group_list, - HA_POS_ERROR, HA_POS_ERROR, FALSE) || - alloc_group_fields(this, group_list) || - make_sum_func_list(all_fields, fields_list, 1) || - prepare_sum_aggregators(sum_funcs, need_distinct) || - setup_sum_funcs(thd, sum_funcs)) - { - DBUG_RETURN(1); - } - group_list=0; - } - else - { - if (make_sum_func_list(all_fields, fields_list, 0) || - prepare_sum_aggregators(sum_funcs, need_distinct) || - setup_sum_funcs(thd, sum_funcs)) - { - DBUG_RETURN(1); - } - - if (!group_list && ! exec_tmp_table1->distinct && order && simple_order) - { - thd_proc_info(thd, "Sorting for order"); - if (create_sort_index(thd, this, order, - HA_POS_ERROR, HA_POS_ERROR, TRUE)) - { - DBUG_RETURN(1); - } - order=0; - } - } - - /* - Optimize distinct when used on some of the tables - SELECT DISTINCT t1.a FROM t1,t2 WHERE t1.b=t2.b - In this case we can stop scanning t2 when we have found one t1.a - */ - - if (exec_tmp_table1->distinct) - { - table_map used_tables= thd->used_tables; - JOIN_TAB *last_join_tab= join_tab+tables-1; - do - { - if (used_tables & last_join_tab->table->map) - break; - last_join_tab->not_used_in_distinct=1; - } while (last_join_tab-- != join_tab); - /* Optimize "select distinct b from t1 order by key_part_1 limit #" */ - if (order && skip_sort_order) - { - /* Should always succeed */ - if (test_if_skip_sort_order(&join_tab[const_tables], - order, unit->select_limit_cnt, 0, - &join_tab[const_tables].table-> - keys_in_use_for_order_by)) - order=0; - } - } - - /* If this join belongs to an uncacheable query save the original join */ - if (select_lex->uncacheable && init_save_join_tab()) - DBUG_RETURN(-1); /* purecov: inspected */ + if (!(select_options & SELECT_DESCRIBE)) + { + having= 0; } error= 0; @@ -2867,20 +2727,41 @@ */ curr_join->examined_rows= 0; + /* + The loose index scan access method guarantees that all grouping or + duplicate row elimination (for distinct) is already performed + during data retrieval, and that all MIN/MAX functions are already + computed for each group. Thus all MIN/MAX functions should be + treated as regular functions, and there is no need to perform + grouping in the main execution loop. + Notice that currently loose index scan is applicable only for + single table queries, thus it is sufficient to test only the first + join_tab element of the plan for its access method. + */ + if (join_tab && join_tab->is_using_loose_index_scan()) + tmp_table_param.precomputed_group_by= + !join_tab->is_using_agg_loose_index_scan(); + /* Create a tmp table if distinct or if the sort is too complicated */ if (need_tmp) { + if (!exec_tmp_table1) + { + if (create_tmp_table_for_sort()) + DBUG_VOID_RETURN; + } + curr_tmp_table= exec_tmp_table1; + if (tmp_join) { /* - We are in a non cacheable sub query. Get the saved join structure - after optimization. + We are in a non-cacheable subquery. Get the saved join structure + after creation of temporary table. (curr_join may have been modified during last exection and we need to reset it) */ curr_join= tmp_join; } - curr_tmp_table= exec_tmp_table1; /* Copy data to the temporary table */ thd_proc_info(thd, "Copying to tmp table"); @@ -3309,6 +3190,129 @@ DBUG_VOID_RETURN; } +/** + @todo Investigate how to unify this code with the code for creating a + second temporary table, @c exec_tmp_table2. @see JOIN::exec +*/ +int +JOIN::create_tmp_table_for_sort() +{ + DBUG_ENTER("JOIN::create_tmp_table_for_sort"); + thd_proc_info(thd, "Creating tmp table"); + + init_items_ref_array(); + + tmp_table_param.hidden_field_count= (all_fields.elements - + fields_list.elements); + ORDER *tmp_group= ((!simple_group && !procedure && + !(test_flags & TEST_NO_KEY_GROUP)) ? group_list : + (ORDER*) 0); + /* + Pushing LIMIT to the temporary table creation is not applicable + when there is ORDER BY or GROUP BY or there is no GROUP BY, but + there are aggregate functions, because in all these cases we need + all result rows. + */ + ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order) && + !tmp_group && + !thd->lex->current_select->with_sum_func) ? + select_limit : HA_POS_ERROR; + + if (!(exec_tmp_table1= + create_tmp_table(thd, &tmp_table_param, all_fields, + tmp_group, group_list ? 0 : select_distinct, + group_list && simple_group, + select_options, tmp_rows_limit, ""))) + DBUG_RETURN(1); + + /* + We don't have to store rows in temp table that doesn't match HAVING if: + - we are sorting the table and writing complete group rows to the + temp table. + - We are using DISTINCT without resolving the distinct as a GROUP BY + on all columns. + + If having is not handled here, it will be checked before the row + is sent to the client. + */ + if (tmp_having && + (sort_and_group || (exec_tmp_table1->distinct && !group_list))) + having= tmp_having; + + /* if group or order on first table, sort first */ + if (group_list && simple_group) + { + DBUG_PRINT("info",("Sorting for group")); + thd_proc_info(thd, "Sorting for group"); + if (create_sort_index(thd, this, group_list, + HA_POS_ERROR, HA_POS_ERROR, FALSE) || + alloc_group_fields(this, group_list) || + make_sum_func_list(all_fields, fields_list, 1) || + prepare_sum_aggregators(sum_funcs, + !join_tab->is_using_agg_loose_index_scan()) || + setup_sum_funcs(thd, sum_funcs)) + { + DBUG_RETURN(1); + } + group_list= 0; + } + else + { + if (make_sum_func_list(all_fields, fields_list, 0) || + prepare_sum_aggregators(sum_funcs, + !join_tab->is_using_agg_loose_index_scan()) || + setup_sum_funcs(thd, sum_funcs)) + { + DBUG_RETURN(1); + } + + if (!group_list && ! exec_tmp_table1->distinct && order && simple_order) + { + thd_proc_info(thd, "Sorting for order"); + if (create_sort_index(thd, this, order, + HA_POS_ERROR, HA_POS_ERROR, TRUE)) + { + DBUG_RETURN(1); + } + order= 0; + } + } + + /* + Optimize distinct when used on some of the tables + SELECT DISTINCT t1.a FROM t1,t2 WHERE t1.b=t2.b + In this case we can stop scanning t2 when we have found one t1.a + */ + + if (exec_tmp_table1->distinct) + { + table_map used_tables= thd->used_tables; + JOIN_TAB *last_join_tab= join_tab+tables-1; + do + { + if (used_tables & last_join_tab->table->map) + break; + last_join_tab->not_used_in_distinct= 1; + } while (last_join_tab-- != join_tab); + /* Optimize "select distinct b from t1 order by key_part_1 limit #" */ + if (order && skip_sort_order) + { + /* Should always succeed */ + if (test_if_skip_sort_order(&join_tab[const_tables], + order, unit->select_limit_cnt, 0, + &join_tab[const_tables].table-> + keys_in_use_for_order_by)) + order= 0; + } + } + + /* If this join belongs to an uncacheable query save the original join */ + if (select_lex->uncacheable && init_save_join_tab()) + DBUG_RETURN(-1); /* purecov: inspected */ + + DBUG_RETURN(0); +} + /** Clean up join. === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-09-06 17:56:05 +0000 +++ b/sql/sql_select.h 2010-09-17 12:34:23 +0000 @@ -1935,6 +1935,15 @@ } private: /** + Create temporary table to be used for sorting needed for + DISTINCT/ORDER BY/GROUP BY. + This creates the table referred to by @c exec_tmp_table1. + + @returns 0 on success, non-zero on failure + */ + int create_tmp_table_for_sort(); + + /** TRUE if the query contains an aggregate function but has no GROUP BY clause. */ --===============0185199794== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/oystein.grovlen@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: oystein.grovlen@stripped\ # flxa41v6adcz2n8r # target_branch: file:///home/og136792/mysql/mysql-next-mr-wl5559/ # testament_sha1: 50a18b4a8767f72736e8ca06c3e5bc108ecc16dc # timestamp: 2010-09-17 14:34:43 +0200 # base_revision_id: jorgen.loland@stripped\ # lygr5tud7qmeut4u # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWde38M8ABgL/gFQwM8F5//// ///f6r////5gDqu7s+7oe9Ozve9noOpPd6nPcwXYPdaQMhIRCdspIQpBKEjRqGRPU2pqeZKY9Rom T1GQ0eiNqaaANAGgBk9QGiE0YCaZQJqNpP1Q00PUAGgaAAAAAB6gaBNCATSeppPTRqPIg9J6gAAA AAAAABIiICGkmGpin5U/UZT01MmI9GkANHqBoAGTINAOGhoyaNGjTQyMhhAGQAyDTQAAMgZAEiRN AEZCYRMgGhpqmnqeSeU0xqep6npNGQ0ABoxLhEKCBzKICMI9GL+HaP8zVq92tsbtb3j1i+mj8exV 7vJi0zETsGtD9ezAOywtZMfg0H7dklsUn7PfPTcrD+/v2b8LPVusxka2ccVFlQtUvM4pFZjiu/6v uRDP1Zr73QDHXxE58cwpN7OmhaN3EjvbfHGe+aFoknEaRiRVnMN8aQ1NGDTzNpP1qpIFTJwqhIoD bTcUs1uRnblMJYuieilBASsnbsmgdph52hAsBNtCBcTQozsBIwaRI0ltZwMQsJZgF2q31FPpyz2E 07kr1i/0B1k1zPa+l2sW7V7Ii91s932ad2aVh4D2jbbA7Ka8+Z4SD/dk73vgG0GWeTBtJwTEb6dM AI6QlSSZFnBqwlmW5j5ulZg1z44lRWcc9jpOzGtzctT/qQ3998NhOq44MilpTXvdJB1qzUh2QbRj nKkHZwkVDmzXXMGeCGZbSvqRLpSk01bCjoOkTA2TwZjOiydTUSO9nZDa1VXgrjDucjjneLxQTrdU yVhjUL+oLSiQhX9hbDGaDY4LCwnVbIHNlYi2/UzFs2oHzbulWTBAbyqDzsxWq+QV4HxmqrgdTZTm Bt2S1iTSvxuBpQTNt7TZNuFu6di88Os461aJMCJiFVT0IJEZ0ZerFosi9/8U4cLnkHtcGmt86XFs OpzeWvP+GqdJlA4b3DQlxlo0RqAmVhcPhLsPiQe65x6orfYy2rtpLljIS2SS2QgbkxQZnFJ01Rvb amiJnyDU1Dd7rokqEZAKBPxSB5h42YXjPCzvMem7t5eXV3uAn1S/CayiJY9THqoOkoFFcjKI/K+d JjG0MSp5I2DCens7MKm7k9BfMHBKkmkRL5vML6ZLheJpaHGzlInfWNDvjociNLG+u8CIxXvnw352 OMImWtI54CAMHUH2D6rDPM0ABzTaWbBDoolEsPeDaOhmiD9LV2Gy+NPtkYT7Aa34B6firPWy9hdE BDAEB9KXjn9Whc4VzaS1k+hAqC/biTAJqRlOxSdnPyJygOilsBrc8iWWjMJAwrivFx9DVVuinKXS pvTXIdWymJW7kaZIV8tSBo3wDO730jqR0dcfsfUzf5jsv36jeZliQaM7zPeKk0LcJcBQlw98JTqO krqrnsXBrgKIUi2dQYYrMEnLbTkqofp9XPppuB2q0CkLyXJB5GLHca/j0iXti7eBbbhttj4WhvIP JhfWT2BacWazzTArVUvh0TXMLXx6HpOqNDQ37dpnbmb5ihBcc5lpv4k9+uU80Kz4UrNhlJ4AMDXU VhawwpFOEBukistLq3l8gF5subLIAwpWGYrmOZJVlAK58R4S56zpK7wmdptKaxu6LgsLsiRyA16w BeagnCM2tv0ndI08dRUkJz2FsDdoBwpdamASghuGdRMOVC83KHiP5rZkoDZunPcKMMbcdCvL4lvC KbIOzuU6140vGaQvqcBdcMFRAMpK/wAx1SCyyCQLDOhtZmlAJTwBLHJvitVmY1GiOCM5cYsr2wK1 9BmxekCGORxmFSnVzBbLHdyLTcTjmsrmQFUt7VTZwHjtxz1nkA266XtLIyyYK5MWJwkLMzpXpbLU 3slWYGCrYNs5bFES1AAYLaHzAnVHMmcVWnIwMbEhTiCeKBYRe4VmisYotwfRey99XTBtrBkywmOd hLOZKI8u7tnJNkHFffrvPjtDzWLEGJpdnh7CqGZQSNCWUGg6ZOj9vP9A0U5X5suEabfo9CCovH/T 7Cc6N6IG85z8+QM7x9xorfT3L3qIiF5MC0uQZ0ETotppqmOSZAcH1+zGiH8u1jIBMmiN11I6YKuG pkRHXblRFXaoHGGBMKEgbC12O7L7hBQVhb9H1omjfVZ3eCMRTnijUgc0vuurS796P7orRwfG/DYp 4olt3rIo83rinSmwisS0KEdwgHkIkWdy64oZYLgT94axy3ZJEPPJTSFiW3djuh7Bh8fR7TnvN94n xhTuoLkFIsZBB8kElthA67sEosUQwaGSOxFBqU8UVqDeEB02Ks+HvtLP3oeQhhqvAvKJljIMkkxI vVZPICuAKTwR0fUPxOfuwFqA961EvooC9zWrKF9MoJNtJBeRJaZoXnn+oBraePaqI8gXM3589rOG X687Ql5IPQpZOSxDK02VZMo0YlclMNXNSrUUKdG4P2zRCDS6LETLm7i0LpBNxtfUUxSVpODUyorb pbGjzZn3M+3rGUwIy+Y8nGMpXQtc3svXyjDya0sjERlQQMW/jkKlU6mQE0GJyVMjal85jTI8G2Xy I+oLi4DYQfRPWuobEyqnAPK+bePOka7nxGrTw2ZWQsIh1QhSN7AwEuMeOi+8pE+s6KwhkkXi7Ewo wbYhtJhfnf9tZToAaNi70N2MUmNha2m4gjxiOclPd3eU+HE+virw7VTK0NgVUQwnVDWSaM+nz6Ae glbolss2h5gVjWCDwnnXAyTPdK0ny1wK8AWq1h1hBf0o2jLiyKwaKhT72s5o8+Uwtd9sk4LvzzE4 ROOKatK4Vltp2Vgc9aYaobZzYxWla2oqd9pJEDDmQILEIQTMhPryAGTDVvYCW5OoyObTBRrokmjP JEhUMzvEINq54iaqGkHagsLRBhSbggM8a7WoU6rBsrQr5EocQWmab0VmHA9IVuSjXb+ZzKF6jXuH hVjF0gbGVZeskpiCCPI799N4gMxBx7R4vgr0GkWQucJIL6cr78oaOKmQLu1hgwOluIWCOgu306Zy MjXbXlPxMIuljyWpWJ65+m2aUrkyAuhE1wzCBo0R9fbubGymKO4X3gGsfzwDfdFtOW07TWWVORj6 Amek51FFy5yfaKw/h95w8YyaKvw6wMjRcRUXJgVoNfckXRCXnlUgkHhWy0QobVkmitYtFoSUJTvr 6loekLlgYDY02G3L86KX0D50GjNOXGa+Ru0Ig5FEcorV9EogybzEpB8LEWrheHgCgXOZwlWttI8c xZVVnYAw1c541ZyVU+W3q0S7Imgzsrseo2L7CFA4bZd1rCuvzrHBBs5UMRNBrDPHRqKOCSDjqmkn 09loipFZUlzfCFvpnjnI0a2eSkolKQ1QV4XvvdfTOsKIYtS3qxYpMXJwn8QYI4CXIRuKh62M28g3 q1uTAyHfq37Fv5YyPsqIXnOANhrxZGYt8+ZTejQudXovaZ4BbQrvMKytMBURw47Q7u92dzy8aqbs UHYaMYwOLQQzehQQgMVARDCX00EGd0LnEUSr0XTw9imr7M6hBx5mamjwNLZrr8oWwrUUKp/NXLjc 6XgeLwN9YeGgqNCadqRj3Pn1brCp1lijWuhaqJbwhLvtBmUty7SxaVN4QhK9WhZVGYhbmxUs8cVt IavmEyBksiYZPipBTllEDHVBQI+ABkygbiEBnHkixigQwq60VENDQOKg3moICYrn1RUqW2kJhvPI uJto2fKrJDWC7jL2r2ibFqULXuDv7s4gVJZSSqDIfPNN9iWSVJWmiVq9QgsCu+sUCXolCMvLaQmX XSbCqfyXQdaqFUTAvx3QdArOo51sk2xNg64QdxChHiRPvjVYPz9nhnXbwCuESN6S4JmMrSlHAnHV xF2/vCFjSeP723VIwdWL7TwpnX8OtcgssA5JTalzugQEIaBmxQQJGnUHIYyKDlIkkpjIbhA4qEjl QkI2QZ1uWYe8ylZOnBd9BagvsW8kswD5V7g1lZ6fcVrRNts0ahkNEiWxYuBrvB4VCqg1sQWhc4RL SW+pKtqYbhnbLNvS0jlZDMi/DVp0J5ZlVMLSgdvV0ytJmFcXvVPH1wjgS9Q6YhX1itl+vQQvc0GS qYy3LCFnOVNBRioxZnEoiHxKOHTjNs23Ob5KUgNyka2Nio6/HFxbe41HCeBmaILyC4xdtG7orYoC 6ZNZW2ItWZm00O3XqjXmSiEd+BcvNakW+j1euPbPqwi8l8bLTCrX5IUhYaIopmwN2zit0VFAM2iD dO+8oiLtJwklJVYhReLVx5JsmIQQb4YEPARFBGOKGKcyUGcpvbt908Y1i5cGehWYZnp/s2xdbdHW jRQwtwAqSMbl0cTPacCo0aEeh+bELrbbc+BWaVHQpAURKmmAVNFu4ksUDZ3dcEoDkwh/gwsrM2Fz SJZRqWMxGN17Rl4+i2e8YbqExyRMDuQWeyACGjytVMzSrj8Rbib9XOeVBAjXS2iIWx02g9pgaeD2 5zXAQfog3MS8eeDFiYKm6JvN5sIn84MsZJaHmsXXbuD1B8aqpxOmQLZC7G9AisDXOMSMBma6bp9R ybgdJwcOI+LRPU5o9WRvDFX6HFfAClLcQkmyoPYpGjVEcVQRCabrqaOhr1prN73QV6sSLAO2BLxL koFx26rTXJFJFFla0Ts6lYFZ6D3c6DBmoB2YLsAty/wrUtK3Q4BdzuekCgEMoqRC/8XckU4UJDXt /DPA --===============0185199794==--