WL#6737: InnoDB: Enabling InnoDB temp-tables as default internal SE for MySQL Optimizer

Status: Complete   —   Priority: Medium

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.


- 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)


- 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. */
        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)