WL#7082: Move permanent transformations from JOIN::optimize () to JOIN::prepare ()
GOAL: save CPU time, memory, avoid future "prepared statement" bugs, make code simpler. MEANS: Refactor how the optimizer does permanent transformations to queries (semijoin, in-to-exists, outer-to-inner-join). Pushed to 5.7 on March 3rd, 2014. User Documentation ================== No user documentation required.
No functional requirement. Non-functional requirement: all queries should work as before, same results, no performance decrease. This is a refactoring task.
BACKGROUND ========== This is how DML execution generally works. There may be unknown exceptions. Immediately executed statement works like this: - prepare a single mem_root/arena for the lifetime - parse query, populate SELECT_LEX and TABLE_LIST structures. - prepare query, resolve and transform - make prepared data available working data structures - optimize query, applied to the working data structures - execute query - cleanup after execution - delete data structures Prepared statement works like this: a) PREPARE stmt: - prepare a mem_root for permanent representation of the statement - parse - prepare (resolve and transform) b) EXECUTE stmt: - prepare a mem_root for statement execution - make a working copy of prepared data available in execution mem_root - prepare (resolve only) - optimize - execute - cleanup after query execution - revert preparation - either cleanup statement or repeat execution "optimize" in (b) phase contains a few query transformations which are actually permanent; they are permanent but because optimization happens in execution phase (with execution memroot), making them permanent forces complicated and buggy code. This WL aims at moving those transformations to the (a) phase. CODE CHANGES ============ Take flatten_subqueries() and simplify_joins() to JOIN::prepare(). Then remove the second copy of "conds" in JOIN::optimize() (first copy is in fix_prepare_information()): sel->prep_where= conds ? conds->real_item()->copy_andor_structure(thd) : NULL; which creates new AND/OR items, which has the bad effect that arguments of old AND/OR items may be restored (by rollback_item_tree_changes()) in vain (causing bugs). Remove sl->prep_where; when JOIN::prepare() ends, sl->where should not change anymore (pointed content can still change), after rollback_item_tree_changes() it should thus be fine for the next execution. So this is implemented: select_lex->where becomes permanent version. join->conds is the optimized copy, valid for one single execution. Remove sl->prep_having, and TABLE_LIST::prep_join_cond too. Advantage: before this worklog, we had that many calls to copy_andor_structure() for the WHERE clause: – prepare (): one call (in fix_prepare_information ()) – optimize (): one call for the first optimization (after simplify_joins ()) – at the start of each execution of the statement: one call (in reinit_stmt_before_use()). So, for one PREPARE and N EXECUTEs, that made 1 + 2 + (N-1) = N+2 calls. After this worklog, it will be N (one call at the start of every optimize ()). So we save two calls, which saves time and memory. For the HAVING clause, we save one call, and also one call per JOIN condition. Some variables are renamed: - for easier identification/search: JOIN::conds to where_cond - for consistency with the first renaming: SELECT_LEX::where to where_cond SELECT_LEX::having to having_cond JOIN::having to having_cond Branch is at my:trunk-wl7082. We also make JOIN::prepare() an almost-JOIN-less member of SELECT_LEX.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.