Merging upstream version 1:11.4.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 190 insertions, 0 deletions

diff --git a/sql/optimizer_defaults.h b/sql/optimizer_defaults.h
new file mode 100644
index 00000000..4eaa3075
--- /dev/null
+++ b/sql/optimizer_defaults.h
@@ -0,0 +1,190 @@
+#ifndef OPTIMIZER_DEFAULTS_INCLUDED
+#define OPTIMIZER_DEFAULTS_INCLUDED
+/*
+   Copyright (c) 2022, MariaDB AB
+
+   This program is free software; you can redistribute it and/or
+   modify it under the terms of the GNU General Public License
+   as published by the Free Software Foundation; version 2 of
+   the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335  USA
+*/
+
+/*
+  This file contains costs constants used by the optimizer
+  All costs should be based on milliseconds (1 cost = 1 ms)
+*/
+
+/* Cost for finding the first key in a key scan */
+#define DEFAULT_KEY_LOOKUP_COST      ((double) 0.000435777)
+
+/* Cost of finding a row based on row_ID */
+#define DEFAULT_ROW_LOOKUP_COST      ((double) 0.000130839)
+
+/*
+  Cost of finding and copying key and row blocks from the storage
+  engine index cache to an internal cache as part of an index
+  scan. This includes all mutexes that needs to be taken to get
+  exclusive access to a page.  The number is taken from accessing an
+  existing blocks from Aria page cache.
+  Used in handler::scan_time() and handler::keyread_time()
+*/
+#define DEFAULT_INDEX_BLOCK_COPY_COST  ((double) 3.56e-05)
+
+/*
+  Cost of copying a row to 'table->record'.
+  Used by scan_time() and rnd_pos_time() methods.
+
+  If this is too small, then table scans will be prefered over 'ref'
+  as with table scans there are no key read (KEY_LOOKUP_COST), fewer
+  disk reads but more record copying and row comparisions.  If it's
+  too big then MariaDB will used key lookup even when table scan is
+  better.
+*/
+#define DEFAULT_ROW_COPY_COST     ((double) 0.000060866)
+
+/*
+  Cost of copying the key to 'table->record'
+
+  If this is too small, then, for small tables, index scans will be
+  prefered over 'ref' as with index scans there are fewer disk reads.
+*/
+#define DEFAULT_KEY_COPY_COST     ((double) 0.000015685)
+
+/*
+  Cost of finding the next index entry and checking its rowid against filter
+  This cost is very low as it's done inside the storage engine.
+  Should be smaller than KEY_COPY_COST.
+ */
+#define DEFAULT_KEY_NEXT_FIND_COST     ((double) 0.000082347)
+
+/* Cost of finding the next row when scanning a table */
+#define DEFAULT_ROW_NEXT_FIND_COST     ((double) 0.000045916)
+
+/**
+  The cost of executing the WHERE clause as part of any row check.
+  Increasing this would force the optimizer to use row combinations
+  that reads fewer rows.
+  The default cost comes from recording times from a simple where clause that
+  compares two fields (date and a double) with constants.
+*/
+#define DEFAULT_WHERE_COST             ((double) 3.2e-05)
+
+/* The cost of comparing a key when using range access or sorting */
+#define DEFAULT_KEY_COMPARE_COST       0.000011361
+
+/* Rowid compare is usually just a single memcmp of a short string */
+#define DEFAULT_ROWID_COMPARE_COST     0.000002653
+/* Rowid copy is usually just a single memcpy of a short string */
+#define DEFAULT_ROWID_COPY_COST        0.000002653
+
+/*
+  Cost modifiers rowid_filter. These takes into account the overhead of
+  using and calling Rowid_filter_sorted_array::check() from the engine
+*/
+#define ROWID_FILTER_PER_CHECK_MODIFIER 4       /* times key_copy_cost */
+#define ROWID_FILTER_PER_ELEMENT_MODIFIER 1     /* times rowid_compare_cost */
+
+/*
+  Average disk seek time on a hard disk is 8-10 ms, which is also
+  about the time to read a IO_SIZE (8192) block.
+
+  A medium ssd is about 400MB/second, which gives us the time for
+  reading an IO_SIZE block to IO_SIZE/400000000 = 0.0000204 sec= 0.02 ms.
+*/
+#define DEFAULT_DISK_READ_COST ((double) IO_SIZE / 400000000.0 * 1000)
+
+/*
+  The follwoing is an old comment for hard-disks, please ignore the
+  following, except if you like history:
+
+  For sequential hard disk seeks the cost formula is:
+    DISK_SEEK_BASE_COST + DISK_SEEK_PROP_COST * #blocks_to_skip
+
+  The cost of average seek
+    DISK_SEEK_BASE_COST + DISK_SEEK_PROP_COST*BLOCKS_IN_AVG_SEEK = 10.
+*/
+
+
+/*
+  The table/index cache_miss/total_cache_request ratio.
+  1.0 means that a searched for key or row will never be in the cache while
+  0.0 means it always in the cache (and we don't have to do any disk reads).
+
+  According to folklore, one should not have to access disk for more
+  than 20% of the cache request for MariaDB to run very well.
+  However in practice when we read rows or keys in a query, we will often
+  read the same row over and over again. Because of this we set
+  DEFAULT_DISK_READ_RATIO to 0.20/10 = 0.02.
+
+  Increasing DISK_READ_RATIO will make MariaDB prefer key lookup over
+  table scans as the impact of ROW_COPY_COST and INDEX_COPY cost will
+  have a larger impact when more rows are examined..
+
+  We are not yet taking into account cache usage statistics as this
+  could confuse users as the EXPLAIN and costs for a query would change
+  between to query calls, which may confuse users (and also make the
+  mtr tests very unpredictable).
+
+  Note that the engine's avg_io_cost() (DEFAULT_DISK_READ_COST by default)
+  is multiplied with this constant!
+*/
+
+#define DEFAULT_DISK_READ_RATIO 0.02
+
+/*
+  The following costs are mainly to ensure we don't do table and index
+  scans for small tables, like the one we have in the mtr test suite.
+
+  This is mostly to keep the mtr tests use indexes (as the optimizer would
+  if the tables are large).  It will also ensure that EXPLAIN is showing
+  more key user for users where they are testing queries with small tables
+  at the start of projects.
+  This is probably OK for most a the execution time difference between table
+  scan and index scan compared to key lookups so small when using small
+  tables. It also helps to fill the index cache which will help mitigate
+  the speed difference.
+*/
+
+/*
+  Extra cost for full table and index scan. Used to prefer key and range
+  over index and table scans
+
+  INDEX_SCAN_SETUP_COST (defined in optimizer_costs.h) is half of
+  table_scan_setup_cost to get the optimizer to prefer index scans to table
+  scans as key copy is faster than row copy and index blocks provides
+  more information in the cache.
+
+  This will also help MyISAM as with MyISAM the table scans has a cost
+  very close to index scans (they are fast but require a read call
+  that we want to avoid even if it's small).
+
+  10 usec is about 10 MyISAM row lookups with optimizer_disk_read_ratio= 0.02
+*/
+#define DEFAULT_TABLE_SCAN_SETUP_COST 0.01          // 10 usec
+
+/* Extra cost for doing a range scan. Used to prefer 'ref' over range */
+#define MULTI_RANGE_READ_SETUP_COST KEY_LOOKUP_COST
+
+/*
+  Temporary file and temporary table related costs
+  Used with subquery materialization, derived tables etc
+*/
+
+#define TMPFILE_CREATE_COST         0.5  // Cost of creating and deleting files
+#define HEAP_TEMPTABLE_CREATE_COST  0.025 // ms
+/* Cost taken from HEAP_LOOKUP_COST in ha_heap.cc */
+#define HEAP_TEMPTABLE_LOOKUP_COST (0.00016097)
+#define DISK_TEMPTABLE_LOOKUP_COST(thd) (tmp_table_optimizer_costs.key_lookup_cost + tmp_table_optimizer_costs.row_lookup_cost + tmp_table_optimizer_costs.row_copy_cost)
+#define DISK_TEMPTABLE_CREATE_COST TMPFILE_CREATE_COST*2 // 2 tmp tables
+#define DISK_TEMPTABLE_BLOCK_SIZE  IO_SIZE
+
+#endif /* OPTIMIZER_DEFAULTS_INCLUDED */