WL#6737: InnoDB: Enabling InnoDB temp-tables as default internal SE for MySQL Optimizer
Currently Optimizer provide an option to select SE for intrinsic temp-table. SE options are MyISAM Or InnoDB. Default is set to MyISAM. This WL aims at making InnoDB default option now that average performance of InnoDB is found to be on par with MyISAM.
Functional: - Switching to InnoDB SE as default SE shouldn't affect any TC from correctness perspective. There could be change in ordering of query result if order by is not used. - Optimizer plan stats might differ but that shouldn't affect the performance. (note: average performance will be considered) Non-Functional: - As InnoDB can be used as SE option for Optimizer there is no need for explicit creation of intrinsic table. innodb_create_intrinsic flag that was added in 5.7.5 will be removed.
- Given that MySQL Optimzier allow InnoDB SE as one of the option to be used for intrinsic temp-table major code changes are already on trunk. - This WL was mainly meant to stablize on performance ans so there are no major changes but minor bug-fixes. --------- Change set: 1. Improve branching logic for InnoDB intrinsic table code flow. 2. For InnoDB intrinsic table created by Optimizer try to port unique index as primary key if possible.
This section lists more detail and actual code changes used to achieve the optimization listed above (in HLS). 1. Reducing branching: Introduce a optimze interface to convert MySQL to InnoDB that helps reduce branching. This was reported as one of the costlier function in perf. /** Convert a row in the MySQL format to a row in the Innobase format. This is specialized function used for intrinsic table with reduce branching. @param[in/out] row row where field values are copied. @param[in] prebuilt prebuilt handler @param[in] mysql_rec row in mysql format. */ static void row_mysql_to_innobase( dtuple_t* row, row_prebuilt_t* prebuilt, const byte* mysql_rec) 2. Unique index to PK (Optimizer Change) Whereever possible try to project unique index as primary key column. 3. Read Optimization While doing index search with EXACT clause if record is not found then avoid scanning of other non-qualifying record. (more of bug fix than optimization)
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.