summaryrefslogtreecommitdiff
path: root/sql/opt_range.h
Commit message (Collapse)AuthorAgeFilesLines
* Fix calculation of selectivityMonty2022-07-071-0/+7
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | calculate_cond_selectivity_for_table() is largely rewritten: - Process keys in the order of rows found, smaller ranges first. If two ranges has equal number of rows, use the one with more key parts. This helps us to mark more used fields to not be used for further selectivity calculations. See cmp_quick_ranges(). - Ignore keys with fields that where used by previous keys - Don't use rec_per_key[] to calculate selectivity for smaller secondary key parts. This does not work as rec_per_key[] value is calculated in the context of the previous key parts, not for the key part itself. The one exception is if the previous key parts is a constant. Other things: - Ensure that select->cond_selectivity is always between 0 and 1. - Ensure that select->opt_range_condition_rows is never updated to a higher value. It is initially set to the number of rows in table. - We know store in table->opt_range_condition_rows the lowest number of rows that any row-read-method has found so far. Before it was only done for QUICK_SELECT_I::QS_TYPE_ROR_UNION and QUICK_SELECT_I::QS_TYPE_INDEX_MERGE. Now it is done for a lot more methods. See calculate_cond_selectivity_for_table() for details. - Calculate and use selectivity for the first key part of a multiple key part if the first key part is a constant. WHERE key1_part1=5 and key2_part1=5. IF key1 is used, then we can still use selectivity for key2 Changes in test results: - 'filtered' is slighly changed, usually to something slightly smaller. - A few cases where for group by queries the table order changed. This was because the number of resulting rows from a group by query with MIN/MAX is now set to be smaller. - A few index was changed as we know prefer index with more key parts if the number of resulting rows is the same.
* MDEV-24953: 10.5.9 crashes with large IN() listSergei Petrunia2021-02-241-0/+2
| | | | | | | | | | | | | | | | | | The problem was in and_all_keys(), the code of MDEV-9759 which calculates the new tree weight: First, it didn't take into account the case when (next->next_key_part=tmp) == NULL and dereferenced a NULL pointer when getting tmp->weight. Second, "if (param->alloced_sel_args > SEL_ARG::MAX_SEL_ARGS) break" could leave the loop with incorrect value of weight. Fixed by introducing SEL_ARG::update_weight_locally() and calling it at the end of the function. This allows to avoid caring about all the above cases.
* MDEV-9750: Quick memory exhaustion with 'extended_keys=on' ...Sergei Petrunia2021-01-291-1/+62
| | | | | | | | | | | | | | | (Variant #5, full patch, for 10.5) Do not produce SEL_ARG graphs that would yield huge numbers of ranges. Introduce a concept of SEL_ARG graph's "weight". If we are about to produce a graph whose "weight" exceeds the limit, remove the parts of SEL_ARG graph that represent the biggest key parts. Do so until the graph's is within the limit. Includes - debug code to verify SEL_ARG graph weight - A user-visible @@optimizer_max_sel_arg_weight to control the optimization - Logging the optimization into the optimizer trace.
* Merge 10.4 into 10.5Marko Mäkelä2020-05-311-0/+1
|\
| * Merge 10.3 into 10.4Marko Mäkelä2020-05-301-0/+1
| |\
| | * Merge 10.2 into 10.3Marko Mäkelä2020-05-291-0/+1
| | |\
| | | * MDEV-21958 Query having many NOT-IN clauses running forever and causing ↵Sergei Golubchik2020-05-271-0/+1
| | | | | | | | | | | | | | | | | | | | | | | | available free memory to use completely let thd->killed to abort range optimizer
* | | | MDEV-274 The data type for IPv6/IPv4 addresses in MariaDBAlexander Barkov2019-10-081-0/+1
|/ / /
* | | MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ↵Varun2019-06-141-4/+6
| | | | | | | | | | | | | | | | | | | | | | | | [Warning] InnoDB: Using a partial-field key prefix in search For a key with keyparts (k1,k2,k3) , if we are building a range over the keyparts we should make sure that if min_value/max_value for a keypart is not added to key buffer then the keyparts following should also not be allowed.
* | | MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectlyVarun Gupta2019-05-281-2/+5
| | | | | | | | | | | | | | | | | | | | | | | | Changed the function append_range_all_keyparts to use sel_arg_range_seq_init / sel_arg_range_seq_next to produce ranges. Also adjusted to print format for the ranges, now the ranges are printed as: (keypart1_min, keypart2_min,..) OP (keypart1_name,keypart2_name, ..) OP (keypart1_max,keypart2_max, ..) Also added more tests for range and index merge access for optimizer trace
* | | Merge branch '10.3' into 10.4Oleksandr Byelkin2019-05-191-1/+1
|\ \ \ | |/ /
| * | Merge 10.2 into 10.3Marko Mäkelä2019-05-141-1/+1
| |\ \ | | |/
| | * Merge 10.1 into 10.2Marko Mäkelä2019-05-131-1/+1
| | |\
| | | * Merge branch '5.5' into 10.1Vicențiu Ciorbaru2019-05-111-1/+1
| | | |\
| | | | * Update FSF AddressVicențiu Ciorbaru2019-05-111-1/+1
| | | | | | | | | | | | | | | | | | | | * Update wrong zip-code
* | | | | MDEV-18755 Assertion `inited==INDEX' failed in handler::ha_index_read_mapIgor Babaev2019-02-281-2/+4
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | When the chosen execution plan accesses a join table employing a range rowid filter a quick select to scan this range has to be built. This quick select is built by a call of SQL_SELECT::test_quick_select(). At this call the function should allow to evaluate only single index range scans. In order to be able to do this a new parameter was added to this function.
* | | | | MDEV-6111 Optimizer TraceVarun Gupta2019-02-131-1/+1
|/ / / / | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This task involves the implementation for the optimizer trace. This feature produces a trace for any SELECT/UPDATE/DELETE/, which contains information about decisions taken by the optimizer during the optimization phase (choice of table access method, various costs, transformations, etc). This feature would help to tell why some decisions were taken by the optimizer and why some were rejected. Trace is session-local, controlled by the @@optimizer_trace variable. To enable optimizer trace we need to write: set @@optimizer_trace variable= 'enabled=on'; To display the trace one can run: SELECT trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; This task also involves: MDEV-18489: Limit the memory used by the optimizer trace introduces a switch optimizer_trace_max_mem_size which limits the memory used by the optimizer trace. This was implemented by Sergei Petrunia.
* | | | Merge 10.2 into 10.3Marko Mäkelä2018-08-281-1/+4
|\ \ \ \ | |/ / /
| * | | Added a new parameter for the function eq_ranges_exceeds_limit()Igor Babaev2018-08-241-1/+2
| | | | | | | | | | | | | | | | introduced in the patch fo MDEV-16934.
| * | | MDEV-16934 Query with very large IN clause lists runs slowlyIgor Babaev2018-08-171-1/+3
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This patch introduces support for the system variable eq_range_index_dive_limit that existed in MySQL starting from 5.6. The variable sets a limit for index dives into equality ranges. Index dives are performed by optimizer to estimate the number of rows in range scans. Index dives usually provide good estimate but they are pretty expensive. To estimate the number of rows in equality ranges statistical data on indexes can be employed. Its usage gives not so good estimates but it's cheap. So if the number of equality dives required by an index scan exceeds the set limit no dives for equality ranges are performed by the optimizer for this index. As the new system variable is introduced in a stable version the default value for it is set to a special value meaning there is no limit for the number of index dives performed by the optimizer. The patch partially uses the MySQL code for WL 5957 'Statistics-based Range optimization for many ranges'.
* | | | System Versioning 1.0 pre3Aleksey Midenkov2017-12-111-0/+17
|\ \ \ \ | | | | | | | | | | | | | | | Merge branch '10.3' into trunk
| * | | | Adding multi_range_read support to partitionsMonty2017-12-031-0/+17
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Other things: - Cleanup of allocated bitmaps done in open(), which simplifies init_partition_bitmaps() - Add needed defines in ha_spider.cc to enable new spider code - Fixed some DBUG_PRINT() to be consistent with normal code - Removed end space - The changes in test cases partition_innodb, partition_range, partition_pruning etc are becasue partitions can now more exactly calculate the number of rows in a range. Contains spider patches: 014,015,023,033,035,037,040,042,044,045,049,050,051,053,059
* | | | | IB, SQL: removed VTQ, added TRT on SQL layer [closes #305]Aleksey Midenkov2017-11-151-0/+32
|/ / / /
* | | | MDEV-12721 Wrong execution plan for WHERE (date_field <=> timestamp_expr AND ↵Alexander Barkov2017-05-071-0/+11
|/ / / | | | | | | | | | TRUE)
* | | MDEV-11836 vcol.vcol_keys_myisam fails in buildbot and outsideSergei Golubchik2017-02-131-1/+0
| | | | | | | | | | | | | | | | | | | | | move TABLE::key_read into handler. Because in index merge and DS-MRR there can be many handlers per table, and some of them use key read while others don't. "keyread" is really per handler, not per TABLE property.
* | | MDEV-11598 Assertion `!table || (!table->read_set || ↵Monty2017-01-111-6/+6
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | bitmap_is_set(table->read_set, field_index))' failed Found and fixed 2 problems: - Filesort addon fields didn't mark virtual columns properly - multi-range-read calculated vcol bitmap but was not using it. This caused wrong vcol field to be calculated on read, which caused the assert.
* | | Removed TABLE->sort to make it possible to have multiple active calls toMonty2016-03-221-0/+3
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | filesort and init_read_record() for the same table. This will simplify code for WINDOW FUNCTIONS (MDEV-6115) - Filesort_info renamed to SORT_INFO and moved to filesort.h - filesort now returns SORT_INFO - init_read_record() now takes a SORT_INFO parameter. - unique declaration is moved to uniques.h - subselect caching of buffers is now more explicit than before - filesort_buffer is now reusable even if rec_length has changed. - filsort_free_buffers() and free_io_cache() calls are removed - Remove one malloc() when using get_addon_fields() Other things: - Added --debug-assert-on-not-freed-memory option to make it easier to debug some not-freed-memory issues.
* | | MDEV-8719 - Obsolete sql_memdup() in favor of THD::memdup() and thd_memdup()Sergey Vojtovich2015-11-261-1/+0
|/ /
* | MDEV-8779: mysqld got signal 11 in sql/opt_range_mrr.cc:100(step_down_to)Sergei Petrunia2015-09-211-2/+2
| | | | | | | | | | | | | | | | | | | | The crash was caused by range optimizer using RANGE_OPT_PARAM::min_key (and max_key) to store keys. Buffer size was a good upper bound for range analysis and partition pruning, but not for EITS selectivity calculations. Fixed by making these buffers variable-size. The sizes are calculated from [pseudo]indexes used for range analysis.
* | MDEV-8610 "WHERE CONTAINS(indexed_geometry_column,1)" causes full table scanAlexander Barkov2015-08-131-1/+610
| |
* | MDEV-8599 "WHERE varchar_field LIKE temporal_const" does not use range optimizerAlexander Barkov2015-08-121-2/+2
| |
* | - Renaming variables so that they don't shadow others (After this patch one ↵Monty2015-07-061-4/+4
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | can compile with -Wshadow and get much fewer warnings) - Changed ER(ER_...) to ER_THD(thd, ER_...) when thd was known or if there was many calls to current_thd in the same function. - Changed ER(ER_..) to ER_THD_OR_DEFAULT(current_thd, ER...) in some places where current_thd is not necessary defined. - Removing calls to current_thd when we have access to thd Part of this is optimization (not calling current_thd when not needed), but part is bug fixing for error condition when current_thd is not defined (For example on startup and end of mysqld) Notable renames done as otherwise a lot of functions would have to be changed: - In JOIN structure renamed: examined_rows -> join_examined_rows record_count -> join_record_count - In Field, renamed new_field() to make_new_field() Other things: - Added DBUG_ASSERT(thd == tmp_thd) in Item_singlerow_subselect() just to be safe. - Removed old 'tab' prefix in JOIN_TAB::save_explain_data() and use members directly - Added 'thd' as argument to a few functions to avoid calling current_thd.
* | MDEV-7951 - sql_alloc() takes 0.25% in OLTP ROSergey Vojtovich2015-05-131-3/+3
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | sql_alloc() has additional costs compared to direct mem_root allocation: - function call: it is defined in a separate translation unit and can't be inlined - it needs to call pthread_getspecific() to get THD::mem_root It is called dozens of times implicitely at least by: - List<>::push_back() - List<>::push_front() - new (for Sql_alloc derived classes) - sql_memdup() Replaced lots of implicit sql_alloc() calls with direct mem_root allocation, passing through THD pointer whenever it is needed. Number of sql_alloc() calls reduced 345 -> 41 per OLTP RO transaction. pthread_getspecific() overhead dropped 0.76 -> 0.59 sql_alloc() overhed dropped 0.25 -> 0.06
* | MDEV-7920 main.group_min_max fails in buildbot with valgrindAlexander Barkov2015-04-131-0/+1
| |
* | Merge 10.0.14 into 10.1Sergei Golubchik2014-10-151-0/+15
|\ \
| * \ 5.5.39 mergeSergei Golubchik2014-08-071-0/+15
| |\ \ | | |/
| | * MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, ↵Sergey Petrunya2014-07-221-0/+15
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | InnoDB - Filesort has an optmization where it reads only columns that are needed before the sorting is done. - When ref(_or_null) is picked by the join optimizer, it may remove parts of WHERE clause that are guaranteed to be true. - However, if we use quick select, we must put all of the range columns into the read set. Not doing so will may cause us to fail to detect the end of the range.
* | | MDEV-6480: Remove conditions for which range optimizer returned ↵Sergei Petrunia2014-08-271-3/+3
|/ / | | | | | | | | | | | | | | | | | | | | | | | | | | SEL_ARG::IMPOSSIBLE Let range optimizer remove parts of OR-clauses for which range analysis produced SEL_TREE(IMPOSSIBLE). There is no need to remove parts of AND-clauses: either they are inside of OR (and the whole AND-clause will be removed), or the AND-clause is at the top level, in which case the whole WHERE (or ON) is always FALSE and this is a degenerate case which receives special treatment. The removal process takes care not to produce 1-way ORs (in that case we substitute the OR for its remaining member).
* | MDEV-5314 - Compiling fails on OSX using clangSergey Vojtovich2014-02-191-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | This is port of fix for MySQL BUG#17647863. revno: 5572 revision-id: jon.hauglid@oracle.com-20131030232243-b0pw98oy72uka2sj committer: Jon Olav Hauglid <jon.hauglid@oracle.com> timestamp: Thu 2013-10-31 00:22:43 +0100 message: Bug#17647863: MYSQL DOES NOT COMPILE ON OSX 10.9 GM Rename test() macro to MY_TEST() to avoid conflict with libc++.
* | Merge 10.0-base -> 10.0Sergey Petrunya2013-10-161-18/+12
|\ \
| * | MDEV-3798: EXPLAIN UPDATE/DELETESergey Petrunya2013-10-081-0/+2
| | | | | | | | | | | | | | | - Update test results after last few csets - Generate correct value for `possible_keys` column for single table UPDATE/DELETE.
| * | MDEV-3798: EXPLAIN UPDATE/DELETESergey Petrunya2013-10-071-7/+7
| | | | | | | | | | | | - Better EXPLAIN-saving methods for quick selects
| * | MDEV-3798: EXPLAIN UPDATE/DELETESergey Petrunya2013-10-051-7/+7
| | | | | | | | | | | | | | | - Address review feedback: more renames
| * | MDEV-3798: EXPLAIN UPDATE/DELETESergey Petrunya2013-10-051-8/+8
| | | | | | | | | | | | - Address review feedback: rename nearly any name used by the new EXPLAIN code.
| * | MDEV-5067: Valgrind warnings (Invalid read) in QPF_table_access::print_explainSergey Petrunya2013-09-261-7/+7
| | | | | | | | | | | | | | | | | | - Query plan footprint (in new terms, "EXPLAIN structure") should always keep a copy of key_name. This is because the table might be a temporary table which may be already freed by the time we use query plan footprint.
| * | [SHOW] EXPLAIN UPDATE/DELETESergey Petrunya2013-09-171-13/+9
| | | | | | | | | | | | | | | | | | | | | - Make QPF structures store data members, not strings. This is not fully possible, because table names (and hence key names, etc) can be deleted, and we have to store strings.
| * | [SHOW] EXPLAIN UPDATE/DELETE, code re-structuringSergey Petrunya2013-06-271-5/+1
| |\ \ | | | | | | | | | | | | | | | | - Merge with 10.0-base
| | * | [SHOW] EXPLAIN UPDATE/DELETE, code re-structuringSergey Petrunya2013-06-271-5/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | - Let Query Plan Footprint store join buffer type in binary form, not string. - Same for LooseScan type.
* | | | 10.0-monty mergeSergei Golubchik2013-07-211-2/+2
|\ \ \ \ | |/ / / |/| | | | | | | | | | | | | | | | | | | | | | | includes: * remove some remnants of "Bug#14521864: MYSQL 5.1 TO 5.5 BUGS PARTITIONING" * introduce LOCK_share, now LOCK_ha_data is strictly for engines * rea_create_table() always creates .par file (even in "frm-only" mode) * fix a 5.6 bug, temp file leak on dummy ALTER TABLE
| * | | Temporary commit of 10.0-mergeMichael Widenius2013-03-261-2/+2
| | | |
View Lorry Controller Status