diff -ru mysql-5.1.24-rc/sql/opt_range.cc mysql-5.1.24-rc-configured/sql/opt_range.cc --- mysql-5.1.24-rc/sql/opt_range.cc 2008-04-08 20:23:03.000000000 +0900 +++ mysql-5.1.24-rc-configured/sql/opt_range.cc 2008-05-26 15:32:18.000000000 +0900 @@ -694,7 +694,7 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, bool index_read_must_be_used, bool update_tbl_stats, - 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, @@ -2132,6 +2132,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", @@ -2302,12 +2303,17 @@ /* Get best 'range' plan and prepare data for making other plans */ if ((range_trp= get_key_scans_params(¶m, tree, FALSE, TRUE, - 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 @@ -3656,7 +3662,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, FALSE, read_time))) + if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time, NULL))) { /* One of index scans in this index_merge is more expensive than entire @@ -4742,11 +4748,12 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, bool index_read_must_be_used, bool update_tbl_stats, - 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"); @@ -4818,6 +4825,11 @@ key_to_read= key; } + if (estimated_records && found_records + && min_records > found_records) + { + min_records = found_records; + } } } @@ -4840,6 +4852,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.1.24-rc/sql/sql_select.cc mysql-5.1.24-rc-configured/sql/sql_select.cc --- mysql-5.1.24-rc/sql/sql_select.cc 2008-04-08 20:23:02.000000000 +0900 +++ mysql-5.1.24-rc-configured/sql/sql_select.cc 2008-05-26 15:36:37.000000000 +0900 @@ -2409,6 +2409,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 */ @@ -4163,7 +4168,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 @@ -4330,7 +4336,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.1.24-rc/storage/innobase/handler/ha_innodb.cc mysql-5.1.24-rc-configured/storage/innobase/handler/ha_innodb.cc --- mysql-5.1.24-rc/storage/innobase/handler/ha_innodb.cc 2008-04-08 20:23:25.000000000 +0900 +++ mysql-5.1.24-rc-configured/storage/innobase/handler/ha_innodb.cc 2008-05-26 15:21:33.000000000 +0900 @@ -5660,10 +5660,28 @@ { ha_rows total_rows; double time_for_scan; + double ret=0.0; 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.1.24-rc/storage/myisam/ha_myisam.cc mysql-5.1.24-rc-configured/storage/myisam/ha_myisam.cc --- mysql-5.1.24-rc/storage/myisam/ha_myisam.cc 2008-04-08 20:23:11.000000000 +0900 +++ mysql-5.1.24-rc-configured/storage/myisam/ha_myisam.cc 2008-05-28 14:52:56.000000000 +0900 @@ -720,6 +720,24 @@ return mi_close(tmp); } +double ha_myisam::read_time(uint index, uint ranges, ha_rows rows) +{ + double ret=0.0; + + ret = handler::read_time(index, ranges, rows); + + /* CHECK ME: these judgements may be too rough */ + /* Big table makes the cost more expensive. */ + if (dflt_key_cache_var.param_buff_size / 4 < stats.index_file_length) + { + ret = ret * 10.0; + if (rows > 10000) + ret = ret * 10.0; + } + + return(ret); +} + int ha_myisam::write_row(uchar *buf) { ha_statistic_increment(&SSV::ha_write_count); diff -ru mysql-5.1.24-rc/storage/myisam/ha_myisam.h mysql-5.1.24-rc-configured/storage/myisam/ha_myisam.h --- mysql-5.1.24-rc/storage/myisam/ha_myisam.h 2008-04-08 20:23:25.000000000 +0900 +++ mysql-5.1.24-rc-configured/storage/myisam/ha_myisam.h 2008-05-26 15:40:44.000000000 +0900 @@ -62,6 +62,7 @@ int open(const char *name, int mode, uint test_if_locked); int close(void); + double read_time(uint index, uint ranges, ha_rows rows); int write_row(uchar * buf); int update_row(const uchar * old_data, uchar * new_data); int delete_row(const uchar * buf);