midenok
commented
4 months ago This change from 4bf48e8bf0d fixes the result of main.distinct:
diff --git sql/opt_costconstants.cc sql/opt_costconstants.cc
index d825fafba4c..2ab0ce4144e 100644
--- sql/opt_costconstants.cc
+++ sql/opt_costconstants.cc
@@ -142,7 +142,7 @@ cost_constant_error Server_cost_constants::set(const LEX_CSTRING &name,
*/
// The cost of reading a block from a main memory buffer pool
-const double SE_cost_constants::MEMORY_BLOCK_READ_COST= 1.0;
+const double SE_cost_constants::MEMORY_BLOCK_READ_COST= 0.25;
// The cost of reading a block from an IO device (disk)@@ -5224,18 +5224,18 @@ INSERT INTO `cc` VALUES
EXPLAIN SELECT `varchar_nokey` g1 FROM cc WHERE `int_nokey` AND `int_key` <= 4
HAVING g1 ORDER BY `varchar_key` LIMIT 6 ;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE cc NULL ALL int_key NULL NULL NULL 20 45.00 Using where; Using filesort
+1 SIMPLE cc NULL range int_key int_key 4 NULL 10 90.00 Using where; Using filesortMEMORY_BLOCK_READ_COST was introduced (splits IO_BLOCK_READ_COST())
commit 07cad655133
Author: Olav Sandstaa <olav.sandstaa@oracle.com>
Date: Sun Jun 28 11:38:26 2015 +0200
WL#7340 IO aware cost estimate function for data access double buffer_block_read_cost(double blocks) const {
// memory_block_read_cost() returns MEMORY_BLOCK_READ_COST
return blocks * m_se_cost_constants->memory_block_read_cost();
} double io_block_read_cost(double blocks) const {
// same for IO_BLOCK_READ_COST
return blocks * m_se_cost_constants->io_block_read_cost();
}buffer_block_read_cost() usage
double Cost_model_table::page_read_cost(double pages) const {
assert(m_initialized);
assert(pages >= 0.0);
const double in_mem = m_table->file->table_in_memory_estimate();
const double pages_in_mem = pages * in_mem;
const double pages_on_disk = pages - pages_in_mem;
assert(pages_on_disk >= 0.0);
const double cost =
buffer_block_read_cost(pages_in_mem) + io_block_read_cost(pages_on_disk);
return cost;
}
double Cost_model_table::page_read_cost_index(uint index, double pages) const {
assert(m_initialized);
assert(pages >= 0.0);
double in_mem = m_table->file->index_in_memory_estimate(index);
const double pages_in_mem = pages * in_mem;
const double pages_on_disk = pages - pages_in_mem;
const double cost =
buffer_block_read_cost(pages_in_mem) + io_block_read_cost(pages_on_disk);
return cost;
}io_block_read_cost() has a bit more usages
disk_seek_base_cost(), disk_seek_prop_cost(), page_read_cost() (again), page_read_cost_index() (again), get_merge_buffers_cost(), get_use_cost()
commit dc1c0eb8b6e (HEAD)
Author: Olav Sandstaa <olav.sandstaa@oracle.com>
Date: Fri Feb 28 09:54:21 2014 +0100
WL#7182 Optimizer Cost Model APIio_block_read_cost() introduced and 1.0 replaced with it.
io_block_read_cost() is added to get_merge_cost(), etc.
commit 250d5fb8bed
Author: Olav Sandstaa <olav.sandstaa@oracle.com>
Date: Wed Jan 8 11:32:25 2014 +0100
WL#7209 Handler interface for new cost modelIO_BLOCK_READ_COST() introduced (replaces index_only_read_time())
commit f33b6d722a5 (HEAD)
Author: Jorgen Loland <jorgen.loland@oracle.com>
Date: Tue Nov 8 12:37:54 2011 +0100
WL#5860: Make COST_VECT a properly encapsulated C++ classindex_only_read_time() introduced (replaces get_index_only_read_time())
commit 33fcd89d9ef
Author: sergefp@mysql.com <>
Date: Sat Mar 10 00:08:24 2007 +0300
WL#2474 "Multi Range Read: Change the default MRR implementation to implement new MRR interface"
WL#2475 "Batched range read functions for MyISAM/InnoDb"
"Index condition pushdown for MyISAM/InnoDB"get_index_only_read_time() introduced (replaces block_size/2 ... calculations)
commit 67c6d5113c7
Author: sergefp@mysql.com <>
Date: Thu Dec 18 06:08:00 2003 +0300
Precise read time estimates for index_merge/Unique
order_by test segfault
Result
frame 3
frame 4
Hypothesis
join->orderis not yet initialized correctly?