diff -ru mysql-5.0.54/libmysqld/ha_innodb.cc mysql-5.0.54-configured/libmysqld/ha_innodb.cc --- mysql-5.0.54/libmysqld/ha_innodb.cc 2007-11-21 02:38:06.000000000 +0900 +++ mysql-5.0.54-configured/libmysqld/ha_innodb.cc 2008-05-13 12:02:41.000000000 +0900 @@ -5307,10 +5307,29 @@ { ha_rows total_rows; double time_for_scan; + double ret=0.0; + row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt; if (index != table->s->primary_key) { /* Not clustered */ - return(handler::read_time(index, ranges, rows)); + ret = handler::read_time(index, ranges, rows); + + /* CHECK ME: these judgements may be too rough */ + /* Big table makes the cost more expensive. */ + if ((prebuilt->table->stat_clustered_index_size + + prebuilt->table->stat_sum_of_other_index_sizes) + > (srv_pool_size / 2)) { + ret = ret * 10.0; + if (rows > 10000) { + ret = ret * 10.0; + } + } + /* If many rows are estimated to return, we should not to use this plan. */ + if (rows > 100000) { + ret = ret * 10.0; + } + + return(ret); } if (rows <= 2) { diff -ru mysql-5.0.54/libmysqld/opt_range.cc mysql-5.0.54-configured/libmysqld/opt_range.cc --- mysql-5.0.54/libmysqld/opt_range.cc 2007-12-23 04:39:19.000000000 +0900 +++ mysql-5.0.54-configured/libmysqld/opt_range.cc 2008-05-08 09:06:50.000000000 +0900 @@ -573,7 +573,7 @@ MEM_ROOT *alloc = NULL); static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, bool index_read_must_be_used, - double read_time); + double read_time, ha_rows *estimated_records); static TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree, double read_time, @@ -1946,6 +1946,7 @@ ha_rows limit, bool force_quick_range) { uint idx; + ha_rows estimated_records=0; double scan_time; DBUG_ENTER("SQL_SELECT::test_quick_select"); DBUG_PRINT("enter",("keys_to_use: %lu prev_tables: %lu const_tables: %lu", @@ -2102,12 +2103,17 @@ /* Get best 'range' plan and prepare data for making other plans */ if ((range_trp= get_key_scans_params(¶m, tree, FALSE, - best_read_time))) + best_read_time, &estimated_records))) { best_trp= range_trp; best_read_time= best_trp->read_cost; } + if (estimated_records) + { + records = estimated_records; + } + /* Simultaneous key scans and row deletes on several handler objects are not allowed so don't use ROR-intersection for @@ -2351,7 +2357,7 @@ { DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map, "tree in SEL_IMERGE");); - if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, read_time))) + if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, read_time, NULL))) { /* One of index scans in this index_merge is more expensive than entire @@ -3428,11 +3434,12 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, bool index_read_must_be_used, - double read_time) + double read_time, ha_rows *estimated_records) { int idx; SEL_ARG **key,**end, **key_to_read= NULL; ha_rows best_records; + ha_rows min_records= HA_POS_ERROR; TRP_RANGE* read_plan= NULL; bool pk_is_clustered= param->table->file->primary_key_is_clustered(); DBUG_ENTER("get_key_scans_params"); @@ -3505,6 +3512,11 @@ key_to_read= key; } + if (estimated_records && found_records + && min_records > found_records) + { + min_records = found_records; + } } } @@ -3527,6 +3539,12 @@ else DBUG_PRINT("info", ("No 'range' table read plan found")); + /* minimum number of records (not 0) as estimated number of records */ + if (estimated_records && min_records != HA_POS_ERROR) + { + *estimated_records = min_records; + } + DBUG_RETURN(read_plan); } diff -ru mysql-5.0.54/libmysqld/sql_select.cc mysql-5.0.54-configured/libmysqld/sql_select.cc --- mysql-5.0.54/libmysqld/sql_select.cc 2007-12-23 04:39:18.000000000 +0900 +++ mysql-5.0.54-configured/libmysqld/sql_select.cc 2008-05-13 12:02:35.000000000 +0900 @@ -2344,6 +2344,11 @@ table->reginfo.impossible_range=1; DBUG_RETURN(0); } + if (error == 0) + { + /* quick select is not effective. but the estimated value is used. */ + DBUG_RETURN(select->records); + } DBUG_PRINT("warning",("Couldn't use record count on const keypart")); } DBUG_RETURN(HA_POS_ERROR); /* This shouldn't happend */ @@ -4059,7 +4064,8 @@ tmp= record_count*(tmp+keys_per_block-1)/keys_per_block; } else - tmp= record_count*min(tmp,s->worst_seeks); + tmp= table->file->read_time(key, (uint)min(record_count,(double)UINT_MAX), + (ha_rows)(record_count * min(tmp,s->worst_seeks))); } } else @@ -4203,7 +4209,8 @@ tmp= record_count*(tmp+keys_per_block-1)/keys_per_block; } else - tmp= record_count*min(tmp,s->worst_seeks); + tmp= table->file->read_time(key, (uint)min(record_count,(double)UINT_MAX), + (ha_rows)(record_count * min(tmp,s->worst_seeks))); } else tmp= best_time; // Do nothing diff -ru mysql-5.0.54/sql/ha_innodb.cc mysql-5.0.54-configured/sql/ha_innodb.cc --- mysql-5.0.54/sql/ha_innodb.cc 2007-11-21 02:38:06.000000000 +0900 +++ mysql-5.0.54-configured/sql/ha_innodb.cc 2008-05-13 11:27:35.000000000 +0900 @@ -5307,10 +5307,29 @@ { ha_rows total_rows; double time_for_scan; + double ret=0.0; + row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt; if (index != table->s->primary_key) { /* Not clustered */ - return(handler::read_time(index, ranges, rows)); + ret = handler::read_time(index, ranges, rows); + + /* CHECK ME: these judgements may be too rough */ + /* Big table makes the cost more expensive. */ + if ((prebuilt->table->stat_clustered_index_size + + prebuilt->table->stat_sum_of_other_index_sizes) + > (srv_pool_size / 2)) { + ret = ret * 10.0; + if (rows > 10000) { + ret = ret * 10.0; + } + } + /* If many rows are estimated to return, we should not to use this plan. */ + if (rows > 100000) { + ret = ret * 10.0; + } + + return(ret); } if (rows <= 2) { diff -ru mysql-5.0.54/sql/opt_range.cc mysql-5.0.54-configured/sql/opt_range.cc --- mysql-5.0.54/sql/opt_range.cc 2007-12-23 04:39:19.000000000 +0900 +++ mysql-5.0.54-configured/sql/opt_range.cc 2008-05-08 09:04:18.000000000 +0900 @@ -573,7 +573,7 @@ MEM_ROOT *alloc = NULL); static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, bool index_read_must_be_used, - double read_time); + double read_time, ha_rows *estimated_records); static TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree, double read_time, @@ -1946,6 +1946,7 @@ ha_rows limit, bool force_quick_range) { uint idx; + ha_rows estimated_records=0; double scan_time; DBUG_ENTER("SQL_SELECT::test_quick_select"); DBUG_PRINT("enter",("keys_to_use: %lu prev_tables: %lu const_tables: %lu", @@ -2102,12 +2103,17 @@ /* Get best 'range' plan and prepare data for making other plans */ if ((range_trp= get_key_scans_params(¶m, tree, FALSE, - best_read_time))) + best_read_time, &estimated_records))) { best_trp= range_trp; best_read_time= best_trp->read_cost; } + if (estimated_records) + { + records = estimated_records; + } + /* Simultaneous key scans and row deletes on several handler objects are not allowed so don't use ROR-intersection for @@ -2351,7 +2357,7 @@ { DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map, "tree in SEL_IMERGE");); - if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, read_time))) + if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, read_time, NULL))) { /* One of index scans in this index_merge is more expensive than entire @@ -3428,11 +3434,12 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, bool index_read_must_be_used, - double read_time) + double read_time, ha_rows *estimated_records) { int idx; SEL_ARG **key,**end, **key_to_read= NULL; ha_rows best_records; + ha_rows min_records= HA_POS_ERROR; TRP_RANGE* read_plan= NULL; bool pk_is_clustered= param->table->file->primary_key_is_clustered(); DBUG_ENTER("get_key_scans_params"); @@ -3505,6 +3512,11 @@ key_to_read= key; } + if (estimated_records && found_records + && min_records > found_records) + { + min_records = found_records; + } } } @@ -3527,6 +3539,12 @@ else DBUG_PRINT("info", ("No 'range' table read plan found")); + /* minimum number of records (not 0) as estimated number of records */ + if (estimated_records && min_records != HA_POS_ERROR) + { + *estimated_records = min_records; + } + DBUG_RETURN(read_plan); } diff -ru mysql-5.0.54/sql/sql_select.cc mysql-5.0.54-configured/sql/sql_select.cc --- mysql-5.0.54/sql/sql_select.cc 2007-12-23 04:39:18.000000000 +0900 +++ mysql-5.0.54-configured/sql/sql_select.cc 2008-05-13 11:29:42.000000000 +0900 @@ -2344,6 +2344,11 @@ table->reginfo.impossible_range=1; DBUG_RETURN(0); } + if (error == 0) + { + /* quick select is not effective. but the estimated value is used. */ + DBUG_RETURN(select->records); + } DBUG_PRINT("warning",("Couldn't use record count on const keypart")); } DBUG_RETURN(HA_POS_ERROR); /* This shouldn't happend */ @@ -4059,7 +4064,8 @@ tmp= record_count*(tmp+keys_per_block-1)/keys_per_block; } else - tmp= record_count*min(tmp,s->worst_seeks); + tmp= table->file->read_time(key, (uint)min(record_count,(double)UINT_MAX), + (ha_rows)(record_count * min(tmp,s->worst_seeks))); } } else @@ -4203,7 +4209,8 @@ tmp= record_count*(tmp+keys_per_block-1)/keys_per_block; } else - tmp= record_count*min(tmp,s->worst_seeks); + tmp= table->file->read_time(key, (uint)min(record_count,(double)UINT_MAX), + (ha_rows)(record_count * min(tmp,s->worst_seeks))); } else tmp= best_time; // Do nothing