revision-id: 98d55b1366746a2b4750da9e8781f66b0d01ed85 (mariadb-10.3.6-117-g98d55b1) parent(s): ccce4d3be9bb5dfce66576f9744bcb927b754cf4 282ba973e748456a829eecf1b49fb352870c6a8f author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-14 22:07:33 -0800 message: Merge branch '10.4' into bb-10.4-mdev16188 client/mysqltest.cc | 9 +- cmake/build_configurations/mysql_release.cmake | 3 + cmake/submodules.cmake | 2 +- cmake/wsrep.cmake | 26 +- extra/innochecksum.cc | 138 +- extra/mariabackup/backup_mysql.cc | 2 +- extra/mariabackup/fil_cur.cc | 32 +- extra/mariabackup/fil_cur.h | 7 +- extra/mariabackup/read_filt.cc | 2 +- extra/mariabackup/read_filt.h | 2 +- extra/mariabackup/write_filt.cc | 12 +- extra/mariabackup/xtrabackup.cc | 56 +- extra/mariabackup/xtrabackup.h | 9 +- include/mysql/service_wsrep.h | 2 +- libmariadb | 2 +- libmysqld/CMakeLists.txt | 4 +- mysql-test/include/galera_suspend.inc | 14 + mysql-test/include/mtr_check.sql | 1 + mysql-test/main/alter_user.result | 2 +- mysql-test/main/create_select_tmp.result | 21 + mysql-test/main/create_select_tmp.test | 14 + mysql-test/main/failed_auth_unixsocket.result | 11 +- mysql-test/main/failed_auth_unixsocket.test | 6 +- mysql-test/main/grant5.result | 22 +- mysql-test/main/grant5.test | 8 - mysql-test/main/information_schema-big.result | 2 + mysql-test/main/information_schema.result | 3 + .../main/information_schema_all_engines.result | 8 +- mysql-test/main/lock_user.result | 134 + mysql-test/main/lock_user.test | 142 + mysql-test/main/lowercase_table.result | 4 + mysql-test/main/lowercase_table.test | 12 + mysql-test/main/mysql_upgrade-6984.result | 2 +- mysql-test/main/mysql_upgrade-6984.test | 2 +- mysql-test/main/mysql_upgrade.result | 41 +- mysql-test/main/mysql_upgrade.test | 35 +- mysql-test/main/mysqld--help.result | 8 + mysql-test/main/opt_trace.result | 3511 ++++++++++++++++++++ mysql-test/main/opt_trace.test | 335 ++ mysql-test/main/opt_trace_index_merge.result | 249 ++ mysql-test/main/opt_trace_index_merge.test | 21 + .../main/opt_trace_index_merge_innodb.result | 248 ++ mysql-test/main/opt_trace_index_merge_innodb.test | 45 + mysql-test/main/opt_trace_security.result | 396 +++ mysql-test/main/opt_trace_security.test | 197 ++ mysql-test/main/plugin_auth.result | 33 +- mysql-test/main/plugin_auth.test | 38 +- mysql-test/main/plugin_auth_qa_1.result | 4 +- mysql-test/main/plugin_auth_qa_1.test | 2 +- mysql-test/main/reset_connection.result | 20 + mysql-test/main/reset_connection.test | 15 + mysql-test/main/system_mysql_db_507.result | 21 + mysql-test/main/system_mysql_db_507.test | 18 + mysql-test/main/system_mysql_db_fix40123.result | 76 +- mysql-test/main/system_mysql_db_fix40123.test | 55 +- mysql-test/main/system_mysql_db_fix50030.result | 80 +- mysql-test/main/system_mysql_db_fix50030.test | 62 +- mysql-test/main/system_mysql_db_fix50117.result | 76 +- mysql-test/main/system_mysql_db_fix50117.test | 87 +- mysql-test/main/type_json.result | 67 +- mysql-test/main/type_json.test | 35 + mysql-test/main/type_timestamp.result | 9 + mysql-test/main/type_timestamp.test | 8 + .../federated/federatedx_create_handlers.result | 312 ++ .../federated/federatedx_create_handlers.test | 161 + mysql-test/suite/funcs_1/r/is_columns_is.result | 8 + .../suite/funcs_1/r/is_columns_is_embedded.result | 8 + .../suite/funcs_1/r/is_routines_embedded.result | 6 +- mysql-test/suite/funcs_1/r/is_tables_is.result | 50 + .../suite/funcs_1/r/is_tables_is_embedded.result | 50 + mysql-test/suite/galera/disabled.def | 2 - mysql-test/suite/galera/r/galera_defaults.result | 4 +- .../galera/r/galera_gcache_recover_manytrx.result | 31 + mysql-test/suite/galera/r/galera_sst_rsync2.result | 2 + .../galera/r/galera_var_load_data_splitting.result | 10 +- mysql-test/suite/galera/r/partition.result | 10 +- .../suite/galera/t/galera_sst_mariabackup.cnf | 2 +- .../galera/t/galera_sst_mariabackup_data_dir.cnf | 2 +- .../t/galera_sst_mariabackup_encrypt_with_key.cnf | 2 +- .../t/galera_sst_mariabackup_table_options.cnf | 2 +- mysql-test/suite/galera/t/galera_sst_mysqldump.cnf | 2 - .../galera/t/galera_sst_mysqldump_with_key.cnf | 2 +- .../galera/t/galera_var_load_data_splitting.test | 3 + .../suite/galera_3nodes/include/galera_suspend.inc | 14 - .../r/galera_ist_gcache_rollover.result | 1 + .../r/galera_safe_to_bootstrap.result | 10 + mysql-test/suite/galera_3nodes/t/galera_garbd.test | 4 +- .../galera_3nodes/t/galera_ist_gcache_rollover.cnf | 6 +- .../t/galera_ist_gcache_rollover.test | 1 + .../galera_3nodes/t/galera_safe_to_bootstrap.test | 15 +- .../suite/galera_sr/r/galera_sr_load_data.result | 2 +- .../r/galera_sr_load_data_splitting.result | 10 + .../suite/galera_sr/t/galera_sr_load_data.test | 2 +- .../suite/innodb/r/alter_varchar_change.result | 493 +++ .../suite/innodb/r/instant_alter_bugs.result | 47 + .../innodb/r/instant_alter_charset,redundant.rdiff | 28 + .../suite/innodb/r/instant_alter_charset.result | 1812 ++++++++++ .../suite/innodb/r/instant_alter_extend,utf8.rdiff | 29 + .../suite/innodb/r/instant_alter_extend.result | 235 ++ .../suite/innodb/r/instant_alter_import.result | 72 + .../suite/innodb/t/alter_varchar_change.test | 360 ++ mysql-test/suite/innodb/t/instant_alter_bugs.test | 49 + .../suite/innodb/t/instant_alter_charset.test | 538 +++ .../innodb/t/instant_alter_extend.combinations | 5 + .../suite/innodb/t/instant_alter_extend.test | 210 ++ .../suite/innodb/t/instant_alter_import.test | 84 + .../innodb_zip/r/prefix_index_liftedlimit.result | 2 +- .../innodb_zip/t/prefix_index_liftedlimit.test | 2 +- .../incremental_ddl_before_backup.result | 32 + .../mariabackup/incremental_ddl_before_backup.test | 50 + mysql-test/suite/mariabackup/mdev-14447.result | 1 + mysql-test/suite/mariabackup/mdev-14447.test | 14 +- mysql-test/suite/plugins/r/auth_ed25519.result | 4 + mysql-test/suite/plugins/r/multiauth.result | 194 ++ mysql-test/suite/plugins/t/auth_ed25519.test | 4 + mysql-test/suite/plugins/t/multiauth.test | 197 ++ .../roles/i_s_applicable_roles_is_default.result | 2 +- .../roles/i_s_applicable_roles_is_default.test | 2 +- mysql-test/suite/sys_vars/r/sysvars_innodb.result | 28 + .../sys_vars/r/sysvars_server_embedded,32bit.rdiff | 139 +- .../sys_vars/r/sysvars_server_embedded.result | 28 + .../r/sysvars_server_notembedded,32bit.rdiff | 155 +- .../sys_vars/r/sysvars_server_notembedded.result | 28 + mysql-test/suite/sys_vars/r/sysvars_wsrep.result | 18 +- .../suite/sys_vars/r/wsrep_debug_basic.result | 22 +- .../r/wsrep_load_data_splitting_basic.result | 16 +- mysql-test/suite/sys_vars/t/wsrep_debug_basic.test | 10 +- mysql-test/suite/unit/suite.pm | 2 + .../suite/wsrep/r/wsrep-recover-v25,binlogon.rdiff | 17 + mysql-test/suite/wsrep/r/wsrep-recover-v25.result | 28 + mysql-test/suite/wsrep/t/wsrep-recover-v25.cnf | 7 + .../suite/wsrep/t/wsrep-recover-v25.combinations | 4 + mysql-test/suite/wsrep/t/wsrep-recover-v25.test | 121 + mysys/my_fopen.c | 5 +- plugin/auth_ed25519/CMakeLists.txt | 4 +- plugin/auth_ed25519/server_ed25519.c | 9 +- plugin/auth_gssapi/gssapi_server.cc | 20 +- .../mysql-test/auth_gssapi/multiauth.result | 34 + .../mysql-test/auth_gssapi/multiauth.test | 36 + plugin/auth_gssapi/server_plugin.cc | 34 +- plugin/auth_gssapi/server_plugin.h | 2 +- plugin/auth_gssapi/sspi_server.cc | 17 +- plugin/auth_pam/auth_pam.c | 32 +- plugin/auth_pam/auth_pam_base.c | 9 +- plugin/auth_pam/auth_pam_tool.c | 14 +- plugin/auth_pam/auth_pam_v1.c | 31 +- .../mysql-test/user_variables/basic.result | 2 +- plugin/user_variables/user_variables.cc | 2 +- scripts/mysql_install_db.sh | 29 +- scripts/mysql_secure_installation.sh | 59 +- scripts/mysql_system_tables_data.sql | 9 +- scripts/mysql_system_tables_fix.sql | 6 +- scripts/wsrep_sst_mariabackup.sh | 4 +- scripts/wsrep_sst_rsync.sh | 23 +- sql/CMakeLists.txt | 3 +- sql/derived_handler.cc | 127 + sql/derived_handler.h | 85 + sql/field.cc | 123 +- sql/field.h | 2 +- sql/ha_sequence.cc | 2 +- sql/handler.cc | 5 +- sql/handler.h | 46 +- sql/item_cmpfunc.cc | 9 +- sql/item_subselect.cc | 6 +- sql/lex.h | 1 + sql/my_json_writer.cc | 75 +- sql/my_json_writer.h | 425 ++- sql/mysqld.cc | 11 +- sql/mysqld.h | 5 +- sql/opt_range.cc | 846 ++++- sql/opt_range.h | 2 +- sql/opt_subselect.cc | 66 +- sql/opt_table_elimination.cc | 30 +- sql/opt_trace.cc | 722 ++++ sql/opt_trace.h | 201 ++ sql/opt_trace_context.h | 92 + sql/select_handler.cc | 188 ++ sql/select_handler.h | 72 + sql/set_var.h | 9 + sql/share/errmsg-utf8.txt | 3 + sql/sp_head.cc | 13 + sql/sp_head.h | 1 + sql/sql_acl.cc | 998 +++--- sql/sql_class.cc | 19 +- sql/sql_class.h | 22 +- sql/sql_connect.cc | 1 + sql/sql_db.cc | 8 +- sql/sql_derived.cc | 168 +- sql/sql_explain.cc | 28 +- sql/sql_explain.h | 2 + sql/sql_insert.cc | 6 +- sql/sql_lex.cc | 30 +- sql/sql_lex.h | 49 +- sql/sql_load.cc | 64 +- sql/sql_parse.cc | 23 +- sql/sql_parse.h | 2 +- sql/sql_prepare.cc | 12 + sql/sql_priv.h | 14 +- sql/sql_select.cc | 743 ++++- sql/sql_select.h | 46 +- sql/sql_show.cc | 7 + sql/sql_table.cc | 69 +- sql/sql_test.cc | 27 +- sql/sql_test.h | 2 + sql/sql_type.cc | 47 + sql/sql_type.h | 27 +- sql/sql_view.cc | 10 + sql/sql_yacc.yy | 180 +- sql/sql_yacc_ora.yy | 178 +- sql/structs.h | 23 +- sql/sys_vars.cc | 85 +- sql/sys_vars.ic | 2 +- sql/table.cc | 27 +- sql/table.h | 14 + sql/wsrep_mysqld.cc | 69 +- sql/wsrep_mysqld.h | 15 +- sql/wsrep_schema.cc | 79 +- sql/wsrep_schema.h | 19 +- sql/wsrep_server_state.cc | 1 + sql/wsrep_sst.cc | 17 +- sql/wsrep_var.cc | 6 + sql/wsrep_var.h | 2 + storage/federatedx/federatedx_pushdown.cc | 293 ++ storage/federatedx/federatedx_pushdown.h | 63 + storage/federatedx/ha_federatedx.cc | 21 +- storage/federatedx/ha_federatedx.h | 9 + storage/innobase/btr/btr0btr.cc | 119 +- storage/innobase/btr/btr0bulk.cc | 13 +- storage/innobase/btr/btr0cur.cc | 189 +- storage/innobase/btr/btr0defragment.cc | 18 +- storage/innobase/btr/btr0pcur.cc | 2 +- storage/innobase/btr/btr0scrub.cc | 22 +- storage/innobase/btr/btr0sea.cc | 2 +- storage/innobase/buf/buf0buf.cc | 173 +- storage/innobase/buf/buf0dblwr.cc | 82 +- storage/innobase/buf/buf0dump.cc | 8 +- storage/innobase/buf/buf0flu.cc | 11 +- storage/innobase/buf/buf0lru.cc | 68 +- storage/innobase/buf/buf0rea.cc | 99 +- storage/innobase/data/data0data.cc | 8 +- storage/innobase/data/data0type.cc | 21 +- storage/innobase/dict/dict0boot.cc | 2 +- storage/innobase/dict/dict0crea.cc | 24 +- storage/innobase/dict/dict0dict.cc | 64 +- storage/innobase/dict/dict0stats.cc | 4 +- storage/innobase/fil/fil0crypt.cc | 103 +- storage/innobase/fil/fil0fil.cc | 142 +- storage/innobase/fil/fil0pagecompress.cc | 3 +- storage/innobase/fsp/fsp0file.cc | 43 +- storage/innobase/fsp/fsp0fsp.cc | 441 +-- storage/innobase/fts/fts0fts.cc | 4 +- storage/innobase/fts/fts0que.cc | 8 +- storage/innobase/fut/fut0lst.cc | 64 +- storage/innobase/gis/gis0rtree.cc | 8 +- storage/innobase/gis/gis0sea.cc | 11 +- storage/innobase/handler/ha_innodb.cc | 111 +- storage/innobase/handler/handler0alter.cc | 363 +- storage/innobase/handler/i_s.cc | 13 +- storage/innobase/ibuf/ibuf0ibuf.cc | 346 +- storage/innobase/include/btr0btr.h | 57 +- storage/innobase/include/btr0btr.ic | 14 +- storage/innobase/include/btr0cur.h | 16 +- storage/innobase/include/btr0types.h | 10 +- storage/innobase/include/buf0buf.h | 56 +- storage/innobase/include/buf0rea.h | 37 +- storage/innobase/include/data0type.ic | 22 +- storage/innobase/include/dict0dict.h | 34 +- storage/innobase/include/dict0dict.ic | 22 - storage/innobase/include/dict0mem.h | 50 +- storage/innobase/include/fil0crypt.h | 41 +- storage/innobase/include/fil0fil.h | 59 +- storage/innobase/include/fsp0file.h | 19 + storage/innobase/include/fsp0fsp.h | 106 +- storage/innobase/include/fsp0fsp.ic | 69 +- storage/innobase/include/fut0fut.h | 28 +- storage/innobase/include/fut0fut.ic | 68 - storage/innobase/include/ibuf0ibuf.h | 75 +- storage/innobase/include/ibuf0ibuf.ic | 49 +- storage/innobase/include/mem0mem.ic | 4 +- storage/innobase/include/mtr0types.h | 9 +- storage/innobase/include/os0file.h | 2 +- storage/innobase/include/page0size.h | 197 -- storage/innobase/include/page0zip.h | 15 +- storage/innobase/include/page0zip.ic | 21 +- storage/innobase/include/row0ext.h | 9 +- storage/innobase/include/trx0rseg.ic | 5 +- storage/innobase/include/trx0sys.h | 2 +- storage/innobase/include/trx0undo.ic | 8 +- storage/innobase/lock/lock0lock.cc | 2 +- storage/innobase/log/log0log.cc | 8 +- storage/innobase/log/log0recv.cc | 62 +- storage/innobase/mtr/mtr0mtr.cc | 2 +- storage/innobase/os/os0file.cc | 2 - storage/innobase/page/page0zip.cc | 17 +- storage/innobase/rem/rem0rec.cc | 8 +- storage/innobase/row/row0ext.cc | 32 +- storage/innobase/row/row0ftsort.cc | 4 +- storage/innobase/row/row0import.cc | 119 +- storage/innobase/row/row0log.cc | 18 +- storage/innobase/row/row0merge.cc | 22 +- storage/innobase/row/row0mysql.cc | 25 +- storage/innobase/row/row0purge.cc | 2 +- storage/innobase/row/row0row.cc | 4 +- storage/innobase/row/row0sel.cc | 26 +- storage/innobase/row/row0upd.cc | 39 +- storage/innobase/srv/srv0srv.cc | 2 - storage/innobase/srv/srv0start.cc | 13 +- storage/innobase/trx/trx0rec.cc | 34 +- storage/innobase/trx/trx0rseg.cc | 20 +- storage/innobase/trx/trx0undo.cc | 8 +- storage/spider/CMakeLists.txt | 2 +- .../checksum_table_with_quick_mode_3_deinit.inc | 14 + .../checksum_table_with_quick_mode_3_init.inc | 29 + .../include/direct_sql_with_tmp_table_deinit.inc | 7 + .../include/direct_sql_with_tmp_table_init.inc | 9 + .../spider/bugfix/include/quick_mode_0_deinit.inc | 19 + .../spider/bugfix/include/quick_mode_0_init.inc | 51 + .../spider/bugfix/include/quick_mode_1_deinit.inc | 19 + .../spider/bugfix/include/quick_mode_1_init.inc | 51 + .../spider/bugfix/include/quick_mode_2_deinit.inc | 19 + .../spider/bugfix/include/quick_mode_2_init.inc | 51 + .../spider/bugfix/include/quick_mode_3_deinit.inc | 19 + .../spider/bugfix/include/quick_mode_3_init.inc | 51 + .../bugfix/include/slave_trx_isolation_deinit.inc | 15 + .../bugfix/include/slave_trx_isolation_init.inc | 35 + .../bugfix/include/wrapper_mariadb_deinit.inc | 11 + .../spider/bugfix/include/wrapper_mariadb_init.inc | 24 + storage/spider/mysql-test/spider/bugfix/my.cnf | 2 + storage/spider/mysql-test/spider/bugfix/my_1_1.cnf | 44 + storage/spider/mysql-test/spider/bugfix/my_2_1.cnf | 56 + storage/spider/mysql-test/spider/bugfix/my_2_2.cnf | 38 + storage/spider/mysql-test/spider/bugfix/my_2_3.cnf | 8 + storage/spider/mysql-test/spider/bugfix/my_3_1.cnf | 11 + storage/spider/mysql-test/spider/bugfix/my_3_2.cnf | 9 + storage/spider/mysql-test/spider/bugfix/my_3_3.cnf | 9 + storage/spider/mysql-test/spider/bugfix/my_4_1.cnf | 9 + .../r/checksum_table_with_quick_mode_3.result | 100 + .../bugfix/r/direct_sql_with_tmp_table.result | 33 + .../mysql-test/spider/bugfix/r/quick_mode_0.result | 504 +++ .../mysql-test/spider/bugfix/r/quick_mode_1.result | 504 +++ .../mysql-test/spider/bugfix/r/quick_mode_2.result | 504 +++ .../mysql-test/spider/bugfix/r/quick_mode_3.result | 504 +++ .../spider/bugfix/r/slave_trx_isolation.result | 99 + .../spider/bugfix/r/wrapper_mariadb.result | 78 + storage/spider/mysql-test/spider/bugfix/suite.opt | 1 + storage/spider/mysql-test/spider/bugfix/suite.pm | 12 + .../bugfix/t/checksum_table_with_quick_mode_3.cnf | 3 + .../bugfix/t/checksum_table_with_quick_mode_3.test | 72 + .../spider/bugfix/t/direct_sql_with_tmp_table.cnf | 3 + .../spider/bugfix/t/direct_sql_with_tmp_table.test | 35 + .../mysql-test/spider/bugfix/t/quick_mode_0.cnf | 4 + .../mysql-test/spider/bugfix/t/quick_mode_0.test | 156 + .../mysql-test/spider/bugfix/t/quick_mode_1.cnf | 4 + .../mysql-test/spider/bugfix/t/quick_mode_1.test | 156 + .../mysql-test/spider/bugfix/t/quick_mode_2.cnf | 4 + .../mysql-test/spider/bugfix/t/quick_mode_2.test | 156 + .../mysql-test/spider/bugfix/t/quick_mode_3.cnf | 4 + .../mysql-test/spider/bugfix/t/quick_mode_3.test | 157 + .../spider/bugfix/t/slave_trx_isolation.cnf | 4 + .../spider/bugfix/t/slave_trx_isolation.test | 95 + .../mysql-test/spider/bugfix/t/wrapper_mariadb.cnf | 3 + .../spider/bugfix/t/wrapper_mariadb.test | 69 + .../checksum_table_with_quick_mode_3_deinit.inc | 3 +- .../checksum_table_with_quick_mode_3_init.inc | 2 + .../r/checksum_table_with_quick_mode_3.result | 8 +- .../spider/mysql-test/spider/r/quick_mode_0.result | 7 - .../spider/mysql-test/spider/r/quick_mode_1.result | 7 - .../spider/mysql-test/spider/r/quick_mode_2.result | 7 - .../spider/mysql-test/spider/r/quick_mode_3.result | 7 - .../mysql-test/spider/r/slave_trx_isolation.result | 9 +- .../spider/t/checksum_table_with_quick_mode_3.test | 10 +- .../spider/mysql-test/spider/t/quick_mode_0.test | 15 - .../spider/mysql-test/spider/t/quick_mode_1.test | 15 - .../spider/mysql-test/spider/t/quick_mode_2.test | 15 - .../spider/mysql-test/spider/t/quick_mode_3.test | 15 - .../mysql-test/spider/t/slave_trx_isolation.test | 18 - storage/spider/spd_db_handlersocket.cc | 7 +- storage/spider/spd_db_include.cc | 51 + storage/spider/spd_db_include.h | 23 +- storage/spider/spd_db_mysql.cc | 2067 +++++++----- storage/spider/spd_db_mysql.h | 196 +- storage/spider/spd_db_oracle.cc | 9 +- storage/spider/spd_direct_sql.cc | 2 +- storage/spider/spd_include.h | 4 +- storage/spider/spd_sys_table.cc | 17 + storage/spider/spd_sys_table.h | 5 + storage/spider/spd_table.cc | 8 + unittest/strings/json-t.c | 133 +- 388 files changed, 24683 insertions(+), 5424 deletions(-) diff --cc libmysqld/CMakeLists.txt index bf277e9,3146529..43b7fa8 --- a/libmysqld/CMakeLists.txt +++ b/libmysqld/CMakeLists.txt @@@ -121,8 -122,8 +122,9 @@@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc ../sql/proxy_protocol.cc ../sql/backup.cc ../sql/sql_tvc.cc ../sql/sql_tvc.h ../sql/opt_split.cc + ../sql/rowid_filter.cc ../sql/rowid_filter.h ../sql/item_vers.cc + ../sql/opt_trace.cc ${GEN_SOURCES} ${MYSYS_LIBWRAP_SOURCE} ) diff --cc mysql-test/main/mysqld--help.result index 1619f69,47ebfa7..f83bf1b --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@@ -694,7 -692,13 +694,13 @@@ The following specify which files/extr optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived, split_materialized, - condition_pushdown_for_subquery + condition_pushdown_for_subquery, rowid_filter + --optimizer-trace=name + Controls tracing of the Optimizer: + optimizer_trace=option=val[,option=val...], where option + is one of {enabled} and val is one of {on, off, default} + --optimizer-trace-max-mem-size=# + Maximum allowed size of an optimizer trace --optimizer-use-condition-selectivity=# Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial @@@ -1565,7 -1568,9 +1571,9 @@@ old-style-user-limits FALS optimizer-prune-level 1 optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 -optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on +optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on + optimizer-trace + optimizer-trace-max-mem-size 1048576 optimizer-use-condition-selectivity 4 performance-schema FALSE performance-schema-accounts-size -1 diff --cc mysql-test/main/opt_trace.result index 0000000,22cde92..57ed163 mode 000000,100644..100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@@ -1,0 -1,3499 +1,3511 @@@ + SELECT table_name, column_name FROM information_schema.columns where table_name="OPTIMIZER_TRACE"; + table_name column_name + OPTIMIZER_TRACE QUERY + OPTIMIZER_TRACE TRACE + OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE + OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES + show variables like 'optimizer_trace'; + Variable_name Value + optimizer_trace enabled=off + set optimizer_trace="enabled=on"; + show variables like 'optimizer_trace'; + Variable_name Value + optimizer_trace enabled=on + set optimizer_trace="enabled=off"; + create table t1 (a int, b int); + insert into t1 values (1,2),(2,3); + create table t2 (b int); + insert into t2 values (1),(2); + analyze table t1; + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status OK + analyze table t2; + Table Op Msg_type Msg_text + test.t2 analyze status Engine-independent statistics collected + test.t2 analyze status OK + create function f1 (a int) returns INT + return 1; + create view v1 as select * from t1 where t1.a=1; + create view v2 as select * from t1 where t1.a=1 group by t1.b; + set optimizer_trace="enabled=on"; + # Mergeable views/derived tables + select * from v1; + a b + 1 2 + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + select * from v1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "view": { + "table": "v1", + "select_id": 2, + "merged": true + } + }, + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "expanded_query": "/* select#2 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` = 1" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `v1`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = 1", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(1, t1.a)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(1, t1.a)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(1, t1.a)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "selectivity_for_indexes": [], + "selectivity_for_columns": [ + { + "column_name": "a", + "selectivity_from_histograms": 0.5 + } + ] + }, + { + "table": "t1", + "table_scan": { + "rows": 2, + "cost": 2.0044 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 1, + "cost": 2.2044, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = 1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a = 1" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + select * from (select * from t1 where t1.a=1)q; + a b + 1 2 + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + select * from (select * from t1 where t1.a=1)q { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "derived": { + "table": "q", + "select_id": 2, + "merged": true + } + }, + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "expanded_query": "/* select#2 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` = 1" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from (/* select#2 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` = 1) `q`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = 1", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(1, t1.a)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(1, t1.a)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(1, t1.a)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "selectivity_for_indexes": [], + "selectivity_for_columns": [ + { + "column_name": "a", + "selectivity_from_histograms": 0.5 + } + ] + }, + { + "table": "t1", + "table_scan": { + "rows": 2, + "cost": 2.0044 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 1, + "cost": 2.2044, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = 1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a = 1" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + # Non-Mergeable views + select * from v2; + a b + 1 2 + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + select * from v2 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "view": { + "table": "v2", + "select_id": 2, + "materialized": true + } + }, + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "expanded_query": "/* select#2 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` = 1 group by `t1`.`b`" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `v2`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "join_optimization": { + "select_id": 2, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = 1", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(1, t1.a)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(1, t1.a)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(1, t1.a)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "selectivity_for_indexes": [], + "selectivity_for_columns": [ + { + "column_name": "a", + "selectivity_from_histograms": 0.5 + } + ] + }, + { + "table": "t1", + "table_scan": { + "rows": 2, + "cost": 2.0044 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 1, + "cost": 2.2044, + "chosen": true, + "use_tmp_table": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = 1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a = 1" + } + ] + } + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "<derived2>", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "<derived2>", + "table_scan": { + "rows": 2, + "cost": 2 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "<derived2>", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "<derived2>", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [ + { + "join_execution": { + "select_id": 2, + "steps": [] + } + } + ] + } + } + ] + } 0 0 + drop table t1,t2; + drop view v1,v2; + drop function f1; + create table t1(a int, b int); + insert into t1 values (0,0),(1,1),(2,1),(3,2),(4,3), + (5,3),(6,3),(7,3),(8,3),(9,3); + create table t2(a int, b int); + insert into t2 values (0,0),(1,1),(2,1),(3,2),(4,3), + (5,3),(6,3),(7,3),(8,3),(9,3); + ANALYZE TABLE t1; + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status OK + ANALYZE TABLE t2; + Table Op Msg_type Msg_text + test.t2 analyze status Engine-independent statistics collected + test.t2 analyze status OK + create view v1 as select a from t1 group by b; + create view v2 as select a from t2; + # Mergeable view + explain select * from v2 ; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain select * from v2 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "view": { + "table": "v2", + "select_id": 2, + "merged": true + } + }, + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "expanded_query": "/* select#2 */ select `t2`.`a` AS `a` from `t2`" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select `t2`.`a` AS `a` from `v2`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "table_dependencies": [ + { + "table": "t2", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "t2", + "table_scan": { + "rows": 10, + "cost": 2.022 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.022, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t2", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + # Non-Mergeable view + explain select * from v1 ; + id select_type table type possible_keys key key_len ref rows Extra + 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 + 2 DERIVED t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain select * from v1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "view": { + "table": "v1", + "select_id": 2, + "materialized": true + } + }, + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "expanded_query": "/* select#2 */ select `t1`.`a` AS `a` from `t1` group by `t1`.`b`" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select `v1`.`a` AS `a` from `v1`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "join_optimization": { + "select_id": 2, + "steps": [ + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "table_scan": { + "rows": 10, + "cost": 2.022 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.022, + "chosen": true, + "use_tmp_table": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "<derived2>", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "<derived2>", + "table_scan": { + "rows": 10, + "cost": 10 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "<derived2>", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 10, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "<derived2>", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [ + { + "join_execution": { + "select_id": 2, + "steps": [] + } + } + ] + } + } + ] + } 0 0 + drop table t1,t2; + drop view v1,v2; + # + # print ref-keyues array + # + create table t0 (a int); + INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + create table t1 (a int, b int, c int, key(a)); + insert into t1 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B; + create table t2(a int, b int, c int , key(a)); + insert into t2 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B; + analyze table t1; + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status Table is already up to date + analyze table t2; + Table Op Msg_type Msg_text + test.t2 analyze status Engine-independent statistics collected + test.t2 analyze status Table is already up to date + explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 ALL a NULL NULL NULL 100 Using where + 1 SIMPLE t2 ref a a 5 test.t1.b 1 Using where + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t2`.`a` AS `a`,`t2`.`b` AS `b`,`t2`.`c` AS `c` from `t1` join `t2` where `t1`.`a` = `t2`.`b` + 2 and `t2`.`a` = `t1`.`b`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = t2.b + 2 and t2.a = t1.b", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t2", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "a", + "equals": "t2.b + 2", + "null_rejecting": false + }, + { + "table": "t2", + "field": "a", + "equals": "t1.b", + "null_rejecting": true + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "table_scan": { + "rows": 100, + "cost": 2.3174 + } + }, + { + "table": "t2", + "table_scan": { + "rows": 100, + "cost": 2.3174 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.3174, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t1"], + "table": "t2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "cause": "not available", + "rows": 1, + "cost": 200, + "chosen": true + }, + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.3174, + "chosen": false + } + ] + } + } + ] + }, + { + "plan_prefix": [], + "table": "t2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.3174, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t2"], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "cause": "not available", + "rows": 1, + "cost": 200, + "chosen": true + }, + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.3174, + "chosen": false + } + ] + }, + "pruned_by_cost": true + } + ] + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t2.a = t1.b and t1.a = t2.b + 2", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.b is not null" + }, + { + "table": "t2", + "attached": "t1.a = t2.b + 2" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + drop table t1,t2,t0; + # + # group_by min max optimization + # + CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); + analyze table t1; + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status OK + EXPLAIN SELECT DISTINCT a FROM t1; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 range NULL a 4 NULL 5 Using index for group-by + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + EXPLAIN SELECT DISTINCT a FROM t1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select distinct `t1`.`a` AS `a` from `t1`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 65536, + "cost": 13255 + }, + "potential_range_indexes": [ + { + "index": "PRIMARY", + "usable": false, + "cause": "not applicable" + }, + { + "index": "a", + "usable": true, + "key_parts": ["a"] + } + ], + "best_covering_index_scan": { + "index": "a", - "cost": 14627, - "chosen": false, - "cause": "cost" ++ "cost": 4812.5, ++ "chosen": true + }, + "group_index_range": { + "distinct_query": true, + "potential_group_range_indexes": [ + { + "index": "a", + "covering": true, + "rows": 5, - "cost": 7.5 ++ "cost": 6.75 + } + ] + }, + "best_group_range_summary": { + "type": "index_group", + "index": "a", + "group_attribute": null, + "min_aggregate": false, + "max_aggregate": false, + "distinct_aggregate": false, + "rows": 5, - "cost": 7.5, ++ "cost": 6.75, + "key_parts_used_for_access": ["a"], + "ranges": [], + "chosen": true + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "index_group", + "index": "a", + "group_attribute": null, + "min_aggregate": false, + "max_aggregate": false, + "distinct_aggregate": false, + "rows": 5, - "cost": 7.5, ++ "cost": 6.75, + "key_parts_used_for_access": ["a"], + "ranges": [] + }, + "rows_for_plan": 5, - "cost_for_plan": 7.5, ++ "cost_for_plan": 6.75, + "chosen": true + } + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "range", + "resulting_rows": 5, - "cost": 7.5, ++ "cost": 6.75, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + drop table t1; + # + # With group by , where clause and MIN/MAX function + # + CREATE TABLE t1 (a INT, b INT, c int, d int, KEY(a,b,c,d)); + INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (1,0,1,1), (3,2,3,3), (4,5,4,4); + ANALYZE TABLE t1; + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status OK + EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 index NULL a 20 NULL 7 Using where; Using index + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select min(`t1`.`d`) AS `MIN(d)` from `t1` where `t1`.`b` = 2 and `t1`.`c` = 3 group by `t1`.`a`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.b = 2 and t1.c = 3", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(2, t1.b) and multiple equal(3, t1.c)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(2, t1.b) and multiple equal(3, t1.c)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(2, t1.b) and multiple equal(3, t1.c)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 7, + "cost": 5.5291 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a", "b", "c", "d"] + } + ], + "best_covering_index_scan": { + "index": "a", - "cost": 2.7006, ++ "cost": 1.3869, + "chosen": true + }, + "setup_range_conditions": [], + "group_index_range": { + "potential_group_range_indexes": [ + { + "index": "a", + "covering": true, + "ranges": ["2 <= b <= 2 AND 3 <= c <= 3"], + "rows": 8, - "cost": 3.4 ++ "cost": 2.2 + } + ] + }, + "best_group_range_summary": { + "type": "index_group", + "index": "a", + "group_attribute": "d", + "min_aggregate": true, + "max_aggregate": false, + "distinct_aggregate": false, + "rows": 8, - "cost": 3.4, ++ "cost": 2.2, + "key_parts_used_for_access": ["a", "b", "c"], + "ranges": ["2 <= b <= 2 AND 3 <= c <= 3"], + "chosen": false, + "cause": "cost" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + } + } + }, + { + "selectivity_for_indexes": [], + "selectivity_for_columns": [ + { + "column_name": "b", + "selectivity_from_histograms": 0.1667 + }, + { + "column_name": "c", + "selectivity_from_histograms": 0.25 + } + ] + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 0.2917, + "cost": 3.3707, + "chosen": true, + "use_tmp_table": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.b = 2 and t1.c = 3", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.b = 2 and t1.c = 3" + } + ] + } + }, + { + "reconsidering_access_paths_for_index_ordering": { + "clause": "GROUP BY", + "fanout": 1, + "read_time": 3.3717, + "table": "t1", + "rows_estimation": 7, + "possible_keys": [ + { + "index": "a", + "can_resolve_order": true, + "updated_limit": 7, + "index_scan_time": 7, + "records": 7, + "chosen": true + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + DROP TABLE t1; + CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)); + INSERT INTO t1 values (1,'2001-01-01'),(1,'2001-01-02'), + (1,'2001-01-03'),(1,'2001-01-04'), + (2,'2001-01-01'),(2,'2001-01-02'), + (2,'2001-01-03'),(2,'2001-01-04'), + (3,'2001-01-01'),(3,'2001-01-02'), + (3,'2001-01-03'),(3,'2001-01-04'), + (4,'2001-01-01'),(4,'2001-01-02'), + (4,'2001-01-03'),(4,'2001-01-04'); + set optimizer_trace='enabled=on'; + EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id; + id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by ++1 SIMPLE t1 index NULL id 8 NULL 16 Using where; Using index + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`id` AS `id`,min(`t1`.`a`) AS `MIN(a)`,max(`t1`.`a`) AS `MAX(a)` from `t1` where `t1`.`a` >= 20010104e0 group by `t1`.`id`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a >= 20010104e0", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.a >= 20010104e0" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.a >= 20010104e0" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.a >= 20010104e0" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 16, + "cost": 7.3313 + }, + "potential_range_indexes": [ + { + "index": "id", + "usable": true, + "key_parts": ["id", "a"] + } + ], + "best_covering_index_scan": { + "index": "id", - "cost": 4.6269, ++ "cost": 1.8468, + "chosen": true + }, + "setup_range_conditions": [], + "group_index_range": { + "potential_group_range_indexes": [ + { + "index": "id", + "covering": true, + "ranges": ["0x24a20f <= a"], + "rows": 9, - "cost": 3.7 ++ "cost": 2.35 + } + ] + }, + "best_group_range_summary": { + "type": "index_group", + "index": "id", + "group_attribute": "a", + "min_aggregate": true, + "max_aggregate": true, + "distinct_aggregate": false, + "rows": 9, - "cost": 3.7, ++ "cost": 2.35, + "key_parts_used_for_access": ["id"], + "ranges": ["0x24a20f <= a"], - "chosen": true ++ "chosen": false, ++ "cause": "cost" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] - }, - "chosen_range_access_summary": { - "range_access_plan": { - "type": "index_group", - "index": "id", - "group_attribute": "a", - "min_aggregate": true, - "max_aggregate": true, - "distinct_aggregate": false, - "rows": 9, - "cost": 3.7, - "key_parts_used_for_access": ["id"], - "ranges": ["0x24a20f <= a"] - }, - "rows_for_plan": 9, - "cost_for_plan": 3.7, - "chosen": true + } + } ++ }, ++ { ++ "selectivity_for_indexes": [], ++ "selectivity_for_columns": [] + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { - "access_type": "range", - "resulting_rows": 9, - "cost": 3.7, ++ "access_type": "scan", ++ "resulting_rows": 16, ++ "cost": 2.0312, + "chosen": true, + "use_tmp_table": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a >= 20010104e0", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a >= 20010104e0" + } + ] + } ++ }, ++ { ++ "reconsidering_access_paths_for_index_ordering": { ++ "clause": "GROUP BY", ++ "fanout": 1, ++ "read_time": 2.0322, ++ "table": "t1", ++ "rows_estimation": 9, ++ "possible_keys": [ ++ { ++ "index": "id", ++ "can_resolve_order": true, ++ "updated_limit": 16, ++ "index_scan_time": 16, ++ "records": 16, ++ "chosen": true ++ } ++ ] ++ } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id; + id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by ++1 SIMPLE t1 index NULL id 8 NULL 16 Using where; Using index + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`id` AS `id`,`t1`.`a` AS `a` from `t1` where `t1`.`a` = 20010104e0 group by `t1`.`id`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = 20010104e0", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.a = 20010104e0" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.a = 20010104e0" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.a = 20010104e0" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 16, + "cost": 7.3313 + }, + "potential_range_indexes": [ + { + "index": "id", + "usable": true, + "key_parts": ["id", "a"] + } + ], + "best_covering_index_scan": { + "index": "id", - "cost": 4.6269, ++ "cost": 1.8468, + "chosen": true + }, + "setup_range_conditions": [], + "group_index_range": { + "potential_group_range_indexes": [ + { + "index": "id", + "covering": true, + "ranges": ["0x24a20f <= a <= 0x24a20f"], + "rows": 9, - "cost": 3.7 ++ "cost": 2.35 + } + ] + }, + "best_group_range_summary": { + "type": "index_group", + "index": "id", + "group_attribute": null, + "min_aggregate": false, + "max_aggregate": false, + "distinct_aggregate": false, + "rows": 9, - "cost": 3.7, ++ "cost": 2.35, + "key_parts_used_for_access": ["id", "a"], + "ranges": ["0x24a20f <= a <= 0x24a20f"], - "chosen": true ++ "chosen": false, ++ "cause": "cost" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] - }, - "chosen_range_access_summary": { - "range_access_plan": { - "type": "index_group", - "index": "id", - "group_attribute": null, - "min_aggregate": false, - "max_aggregate": false, - "distinct_aggregate": false, - "rows": 9, - "cost": 3.7, - "key_parts_used_for_access": ["id", "a"], - "ranges": ["0x24a20f <= a <= 0x24a20f"] - }, - "rows_for_plan": 9, - "cost_for_plan": 3.7, - "chosen": true + } + } ++ }, ++ { ++ "selectivity_for_indexes": [], ++ "selectivity_for_columns": [] + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { - "access_type": "range", - "resulting_rows": 9, - "cost": 3.7, ++ "access_type": "scan", ++ "resulting_rows": 16, ++ "cost": 2.0312, + "chosen": true, + "use_tmp_table": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = 20010104e0", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a = 20010104e0" + } + ] + } ++ }, ++ { ++ "reconsidering_access_paths_for_index_ordering": { ++ "clause": "GROUP BY", ++ "fanout": 1, ++ "read_time": 2.0322, ++ "table": "t1", ++ "rows_estimation": 9, ++ "possible_keys": [ ++ { ++ "index": "id", ++ "can_resolve_order": true, ++ "updated_limit": 16, ++ "index_scan_time": 16, ++ "records": 16, ++ "chosen": true ++ } ++ ] ++ } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + drop table t1; + # + # Late ORDER BY optimization + # + create table ten(a int); + insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + create table one_k(a int primary key); + insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; + create table t1 ( + pk int not null, + a int, + b int, + c int, + filler char(100), + KEY a_a(c), + KEY a_c(a,c), + KEY a_b(a,b) + ); + insert into t1 + select a, a,a,a, 'filler-dataaa' from test.one_k; + update t1 set a=1 where pk between 0 and 180; + update t1 set b=2 where pk between 0 and 20; + analyze table t1; + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status OK + set optimizer_trace='enabled=on'; + explain select * from t1 where a=1 and b=2 order by c limit 1; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 range a_c,a_b a_c 5 NULL 180 Using where + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain select * from t1 where a=1 and b=2 order by c limit 1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`pk` AS `pk`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t1`.`filler` AS `filler` from `t1` where `t1`.`a` = 1 and `t1`.`b` = 2 order by `t1`.`c` limit 1" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = 1 and t1.b = 2", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "a", + "equals": "1", + "null_rejecting": false + }, + { + "table": "t1", + "field": "a", + "equals": "1", + "null_rejecting": false + }, + { + "table": "t1", + "field": "b", + "equals": "2", + "null_rejecting": false + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 232.66 + }, + "potential_range_indexes": [ + { + "index": "a_a", + "usable": false, + "cause": "not applicable" + }, + { + "index": "a_c", + "usable": true, + "key_parts": ["a", "c"] + }, + { + "index": "a_b", + "usable": true, + "key_parts": ["a", "b"] + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a_c", + "ranges": ["1 <= a <= 1"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 180, - "cost": 217.01, ++ "cost": 231.72, + "chosen": true + }, + { + "index": "a_b", + "ranges": ["1 <= a <= 1 AND 2 <= b <= 2"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 21, - "cost": 26.21, ++ "cost": 27.445, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a_b", + "rows": 21, + "ranges": ["1 <= a <= 1 AND 2 <= b <= 2"] + }, + "rows_for_plan": 21, - "cost_for_plan": 26.21, ++ "cost_for_plan": 27.445, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a_b", + "selectivity_from_index": 0.021 + } + ], + "selectivity_for_columns": [ + { + "column_name": "a", + "selectivity_from_histograms": 0.0012 + }, + { + "column_name": "b", + "selectivity_from_histograms": 0.001 + } + ] + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a_c", + "used_range_estimates": true, + "rows": 180, + "cost": 92, + "chosen": true + }, + { + "access_type": "ref", + "index": "a_b", + "used_range_estimates": true, + "rows": 21, + "cost": 22, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = 1 and t1.b = 2", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + }, + { + "reconsidering_access_paths_for_index_ordering": { + "clause": "ORDER BY", + "fanout": 1, + "read_time": 22.001, + "table": "t1", + "rows_estimation": 21, + "possible_keys": [ + { + "index": "a_a", + "can_resolve_order": true, + "updated_limit": 47, + "index_scan_time": 47, + "usable": false, + "cause": "cost" + }, + { + "index": "a_c", + "can_resolve_order": true, + "updated_limit": 47, + "range_scan_time": 4.324, + "index_scan_time": 4.324, + "records": 180, + "chosen": true + }, + { + "index": "a_b", + "can_resolve_order": false, + "cause": "not usable index for the query" + } + ] + } + }, + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 1202 + }, + "potential_range_indexes": [ + { + "index": "a_a", + "usable": false, + "cause": "not applicable" + }, + { + "index": "a_c", + "usable": true, + "key_parts": ["a", "c"] + }, + { + "index": "a_b", + "usable": false, + "cause": "not applicable" + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a_c", + "ranges": ["1 <= a <= 1"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 180, - "cost": 217.01, ++ "cost": 231.72, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a_c", + "rows": 180, + "ranges": ["1 <= a <= 1"] + }, + "rows_for_plan": 180, - "cost_for_plan": 217.01, ++ "cost_for_plan": 231.72, + "chosen": true + } + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + drop table t1,ten,one_k; + # + # TABLE ELIMINATION + # + create table t1 (a int); + insert into t1 values (0),(1),(2),(3); + create table t0 as select * from t1; + create table t2 (a int primary key, b int) + as select a, a as b from t1 where a in (1,2); + create table t3 (a int primary key, b int) + as select a, a as b from t1 where a in (1,3); + set optimizer_trace='enabled=on'; + analyze table t1; + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status OK + analyze table t2; + Table Op Msg_type Msg_text + test.t2 analyze status Engine-independent statistics collected + test.t2 analyze status OK + analyze table t3; + Table Op Msg_type Msg_text + test.t3 analyze status Engine-independent statistics collected + test.t3 analyze status OK + # table t2 should be eliminated + explain + select t1.a from t1 left join t2 on t1.a=t2.a; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain + select t1.a from t1 left join t2 on t1.a=t2.a { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`a` AS `a` from (`t1` left join `t2` on(`t1`.`a` = `t2`.`a`))" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t2", + "row_may_be_null": true, + "map_bit": 1, + "depends_on_map_bits": ["0"] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t2", + "field": "a", + "equals": "t1.a", + "null_rejecting": true + } + ] + }, + { + "eliminated_tables": ["t2"] + }, + { + "rows_estimation": [ + { + "table": "t1", + "table_scan": { + "rows": 4, + "cost": 2.0068 + } + }, + { + "table": "t2", + "rows": 1, + "cost": 1, + "table_type": "const" + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": ["t2"], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.0068, + "chosen": true + } + ] + } + } + ] + }, + { + "condition_on_constant_tables": "1" + }, + { + "attaching_conditions_to_tables": { + "original_condition": "1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + # no tables should be eliminated + explain select * from t1 left join t2 on t2.a=t1.a; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 + 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain select * from t1 left join t2 on t2.a=t1.a { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`a` AS `a`,`t2`.`a` AS `a`,`t2`.`b` AS `b` from (`t1` left join `t2` on(`t2`.`a` = `t1`.`a`))" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t2", + "row_may_be_null": true, + "map_bit": 1, + "depends_on_map_bits": ["0"] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t2", + "field": "a", + "equals": "t1.a", + "null_rejecting": true + } + ] + }, + { + "eliminated_tables": [] + }, + { + "rows_estimation": [ + { + "table": "t1", + "table_scan": { + "rows": 4, + "cost": 2.0068 + } + }, + { + "table": "t2", + "table_scan": { + "rows": 2, + "cost": 2.0044 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.0068, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t1"], + "table": "t2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "eq_ref", + "index": "PRIMARY", + "rows": 1, + "cost": 4, + "chosen": true + }, + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 8.0176, + "chosen": false + } + ] + } + } + ] + } + ] + }, + { + "condition_on_constant_tables": "1" + }, + { + "attaching_conditions_to_tables": { + "original_condition": "1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + }, + { + "table": "t2", + "attached": "trigcond(trigcond(t1.a is not null))" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + # multiple tables are eliminated + explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`a` AS `a` from (`t1` left join (`t2` join `t3` on(`t2`.`b` = `t3`.`b`)) on(`t2`.`a` = `t1`.`a` and `t3`.`a` = `t1`.`a`))" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t2", + "row_may_be_null": true, + "map_bit": 1, + "depends_on_map_bits": ["0"] + }, + { + "table": "t3", + "row_may_be_null": true, + "map_bit": 2, + "depends_on_map_bits": ["0"] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t2", + "field": "a", + "equals": "t1.a", + "null_rejecting": true + }, + { + "table": "t2", + "field": "a", + "equals": "t3.a", + "null_rejecting": true + }, + { + "table": "t3", + "field": "a", + "equals": "t2.a", + "null_rejecting": true + }, + { + "table": "t3", + "field": "a", + "equals": "t1.a", + "null_rejecting": true + } + ] + }, + { + "eliminated_tables": ["t3", "t2"] + }, + { + "rows_estimation": [ + { + "table": "t1", + "table_scan": { + "rows": 4, + "cost": 2.0068 + } + }, + { + "table": "t2", + "rows": 1, + "cost": 1, + "table_type": "const" + }, + { + "table": "t3", + "rows": 1, + "cost": 1, + "table_type": "const" + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": ["t3", "t2"], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.0068, + "chosen": true + } + ] + } + } + ] + }, + { + "condition_on_constant_tables": "1" + }, + { + "attaching_conditions_to_tables": { + "original_condition": "1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + drop table t0, t1, t2, t3; + # + # IN subquery to sem-join is traced + # + create table t0 (a int); + insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + create table t1(a int, b int); + insert into t1 values (0,0),(1,1),(2,2); + create table t2 as select * from t1; + create table t11(a int, b int); + create table t10 (pk int, a int); + insert into t10 select a,a from t0; + create table t12 like t10; + insert into t12 select * from t10; + analyze table t1,t10; + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status OK + test.t10 analyze status Engine-independent statistics collected + test.t10 analyze status OK + set optimizer_trace='enabled=on'; + explain extended select * from t1 where a in (select pk from t10); + id select_type table type possible_keys key key_len ref rows filtered Extra + 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 + 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 + 2 MATERIALIZED t10 ALL NULL NULL NULL NULL 10 100.00 + Warnings: + Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t10`) where 1 + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain extended select * from t1 where a in (select pk from t10) { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "semijoin", + "chosen": true + } + }, + { + "expanded_query": "/* select#2 */ select `t10`.`pk` from `t10`" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` in (/* select#2 */ select `t10`.`pk` from `t10`)" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "semijoin", + "converted_to_semi_join": true + } + }, + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "1 and t1.a = t10.pk", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(t1.a, t10.pk)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t10", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t1", + "table_scan": { + "rows": 3, + "cost": 2.0066 + } + }, + { + "table": "t10", + "table_scan": { + "rows": 10, + "cost": 2.022 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [ + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t10", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.022, + "chosen": true + } + ] + } + } + ] + } + ] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0066, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t1"], + "table": "t10", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.022, + "chosen": true + } + ] + } + } + ] + }, + { + "plan_prefix": [], + "table": "t10", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.022, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "condition_on_constant_tables": "1" + }, + { + "attaching_conditions_to_tables": { + "original_condition": "1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + }, + { + "table": "t10", + "attached": null + }, + { + "table": "<subquery2>", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + drop table t0,t1,t11,t10,t12,t2; + # + # Selectivities for columns and indexes. + # + create table t0 (a int); + insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + create table t1 ( + pk int, + a int, + b int, + key pk(pk), + key pk_a(pk,a), + key pk_a_b(pk,a,b)); + insert into t1 select a,a,a from t0; + ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a,b) INDEXES (); + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status Table is already up to date + set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; + set @save_use_stat_tables= @@use_stat_tables; + set @@optimizer_use_condition_selectivity=4; + set @@use_stat_tables= PREFERABLY; + set optimizer_trace='enabled=on'; + explain select * from t1 where pk = 2 and a=5 and b=1; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 ref pk,pk_a,pk_a_b pk_a_b 15 const,const,const 1 Using index + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain select * from t1 where pk = 2 and a=5 and b=1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`pk` AS `pk`,`t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`pk` = 2 and `t1`.`a` = 5 and `t1`.`b` = 1" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "pk", + "equals": "2", + "null_rejecting": false + }, + { + "table": "t1", + "field": "pk", + "equals": "2", + "null_rejecting": false + }, + { + "table": "t1", + "field": "a", + "equals": "5", + "null_rejecting": false + }, + { + "table": "t1", + "field": "pk", + "equals": "2", + "null_rejecting": false + }, + { + "table": "t1", + "field": "a", + "equals": "5", + "null_rejecting": false + }, + { + "table": "t1", + "field": "b", + "equals": "1", + "null_rejecting": false + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 10, + "cost": 6.1317 + }, + "potential_range_indexes": [ + { + "index": "pk", + "usable": true, + "key_parts": ["pk"] + }, + { + "index": "pk_a", + "usable": true, + "key_parts": ["pk", "a"] + }, + { + "index": "pk_a_b", + "usable": true, + "key_parts": ["pk", "a", "b"] + } + ], + "best_covering_index_scan": { + "index": "pk_a_b", - "cost": 3.3708, ++ "cost": 1.5429, + "chosen": true + }, + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "pk", + "ranges": ["2 <= pk <= 2"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, - "cost": 2.21, - "chosen": true ++ "cost": 2.3773, ++ "chosen": false, ++ "cause": "cost" + }, + { + "index": "pk_a", + "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, - "cost": 2.21, ++ "cost": 2.3783, + "chosen": false, + "cause": "cost" + }, + { + "index": "pk_a_b", + "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5 AND 1 <= b <= 1"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, - "cost": 2.21, - "chosen": false, - "cause": "cost" ++ "cost": 1.1793, ++ "chosen": true + } + ], + "analyzing_roworder_intersect": { + "intersecting_indexes": [ + { + "index": "pk", - "index_scan_cost": 1, - "cumulateed_index_scan_cost": 1, ++ "index_scan_cost": 1.0023, ++ "cumulateed_index_scan_cost": 1.0023, + "disk_sweep_cost": 0.9008, - "cumulative_total_cost": 1.9008, ++ "cumulative_total_cost": 1.9031, + "usable": true, + "matching_rows_now": 1, + "intersect_covering_with_this_index": false, + "chosen": true + }, + { + "index": "pk_a", + "usable": false, + "cause": "does not reduce cost of intersect" + }, + { + "index": "pk_a_b", + "usable": false, + "cause": "does not reduce cost of intersect" + } + ], + "clustered_pk": { + "clustered_pk_added_to_intersect": false, + "cause": "no clustered pk index" + }, + "chosen": false, - "cause": "too few indexes to merge" ++ "cause": "cost" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", - "index": "pk", ++ "index": "pk_a_b", + "rows": 1, - "ranges": ["2 <= pk <= 2"] ++ "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5 AND 1 <= b <= 1"] + }, + "rows_for_plan": 1, - "cost_for_plan": 2.21, ++ "cost_for_plan": 1.1793, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "pk_a_b", + "selectivity_from_index": 0.1 + } + ], + "selectivity_for_columns": [ + { + "column_name": "a", + "selectivity_from_histograms": 0.1 + }, + { + "column_name": "b", + "selectivity_from_histograms": 0.1 + } + ] + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "pk", + "used_range_estimates": true, + "rows": 1, + "cost": 2, + "chosen": true + }, + { + "access_type": "ref", + "index": "pk_a", + "used_range_estimates": true, + "rows": 1, + "cost": 2, + "chosen": false, + "cause": "cost" + }, + { + "access_type": "ref", + "index": "pk_a_b", + "used_range_estimates": true, + "rows": 1, - "cost": 1, ++ "cost": 1.0043, + "chosen": true + }, + { - "access_type": "range", - "resulting_rows": 1, - "cost": 2.21, - "chosen": false ++ "type": "scan", ++ "chosen": false, ++ "cause": "cost" + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + set @@use_stat_tables= @save_use_stat_tables; + drop table t0,t1; + set optimizer_trace="enabled=off"; + # + # Tests added to show that sub-statements are not traced + # + create table t1(a int); + insert into t1 values (1),(2),(3),(4); + create table t2(a int); + insert into t2 values (1),(2),(3),(4); + create function f1(a int) returns int + begin + declare a int default 0; + set a= a+ (select count(*) from t2); + return a; + end| + create function f2(a int) returns int + begin + declare a int default 0; + select count(*) from t2 into a; + return a; + end| + Warnings: + Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead + set optimizer_trace='enabled=on'; + select f1(a) from t1; + f1(a) + 4 + 4 + 4 + 4 + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + select f1(a) from t1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `f1`(`t1`.`a`) AS `f1(a)` from `t1`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "table_scan": { + "rows": 4, + "cost": 2.0068 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.0068, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + select f2(a) from t1; + f2(a) + 4 + 4 + 4 + 4 + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + select f2(a) from t1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `f2`(`t1`.`a`) AS `f2(a)` from `t1`" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "table_scan": { + "rows": 4, + "cost": 2.0068 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.0068, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + drop table t1,t2; + drop function f1; + drop function f2; + set optimizer_trace='enabled=off'; + # + # MDEV-18489: Limit the memory used by the optimizer trace + # + create table t1 (a int); + insert into t1 values (1),(2); + set optimizer_trace='enabled=on'; + set @save_optimizer_trace_max_mem_size= @@optimizer_trace_max_mem_size; + select * from t1; + a + 1 + 2 + select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + length(trace) + 1889 + set optimizer_trace_max_mem_size=100; + select * from t1; + a + 1 + 2 + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + select * from t1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + 1789 0 + set optimizer_trace_max_mem_size=0; + select * from t1; + a + 1 + 2 + select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + select * from t1 1889 0 + drop table t1; + set optimizer_trace='enabled=off'; + set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; + # + # MDEV-18527: Optimizer trace for DELETE query shows table:null + # + create table ten(a int); + insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + create table t0 (a int, b int); + insert into t0 select a,a from ten; + alter table t0 add key(a); + set optimizer_trace=1; + explain delete from t0 where t0.a<3; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t0 range a a 5 NULL 3 Using where + select * from information_schema.optimizer_trace; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain delete from t0 where t0.a<3 { + "steps": [ + { + "table": "t0", + "range_analysis": { + "table_scan": { + "rows": 10, + "cost": 6.122 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a"] + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no join" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["NULL < a < 3"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 3, - "cost": 4.61, ++ "cost": 5.007, + "chosen": true + } + ], + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 3, + "ranges": ["NULL < a < 3"] + }, + "rows_for_plan": 3, - "cost_for_plan": 4.61, ++ "cost_for_plan": 5.007, + "chosen": true + } + } + } + ] + } 0 0 + drop table ten,t0; + set optimizer_trace='enabled=off'; + # + # MDEV-18528: Optimizer trace support for multi-table UPDATE and DELETE + # + create table ten(a int); + insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + create table t0 (a int, b int); + insert into t0 select a,a from ten; + alter table t0 add key(a); + create table t1 like t0; + insert into t1 select * from t0; + explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t0 range a a 5 NULL 3 Using where + 1 SIMPLE t1 ref a a 5 test.t0.a 1 + select * from information_schema.optimizer_trace; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain delete from t0 where t0.a<3 { + "steps": [ + { + "table": "t0", + "range_analysis": { + "table_scan": { + "rows": 10, + "cost": 6.122 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a"] + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no join" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["NULL < a < 3"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 3, - "cost": 4.61, ++ "cost": 5.007, + "chosen": true + } + ], + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 3, + "ranges": ["NULL < a < 3"] + }, + "rows_for_plan": 3, - "cost_for_plan": 4.61, ++ "cost_for_plan": 5.007, + "chosen": true + } + } + } + ] + } 0 0 + drop table ten,t0,t1; diff --cc mysql-test/main/opt_trace_index_merge.result index 0000000,4c75465..855a725 mode 000000,100644..100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@@ -1,0 -1,249 +1,249 @@@ + set @tmp_opt_switch= @@optimizer_switch; + set optimizer_switch='index_merge_sort_intersection=on'; + set optimizer_trace='enabled=on'; + create table t0 (a int); + insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + create table t1 (a int, b int, c int, filler char(100), + key(a), key(b), key(c)); + insert into t1 select + A.a * B.a*10 + C.a*100, + A.a * B.a*10 + C.a*100, + A.a, + 'filler' + from t0 A, t0 B, t0 C; + This should use union: + explain select * from t1 where a=1 or b=1; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using union(a,b); Using where + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain select * from t1 where a=1 or b=1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t1`.`filler` AS `filler` from `t1` where `t1`.`a` = 1 or `t1`.`b` = 1" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = 1 or t1.b = 1", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(1, t1.a) or multiple equal(1, t1.b)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(1, t1.a) or multiple equal(1, t1.b)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(1, t1.a) or multiple equal(1, t1.b)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 231.69 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a"] + }, + { + "index": "b", + "usable": true, + "key_parts": ["b"] + }, + { + "index": "c", + "usable": false, + "cause": "not applicable" + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_sort_intersect": {}, + "analyzing_index_merge_union": [ + { + "indexes_to_merge": [ + { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["1 <= a <= 1"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, - "cost": 2.21, ++ "cost": 1.1773, + "chosen": true + } + ], + "index_to_merge": "a", - "cumulated_cost": 2.21 ++ "cumulated_cost": 1.1773 + }, + { + "range_scan_alternatives": [ + { + "index": "b", + "ranges": ["1 <= b <= 1"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, - "cost": 2.21, ++ "cost": 1.1773, + "chosen": true + } + ], + "index_to_merge": "b", - "cumulated_cost": 4.42 ++ "cumulated_cost": 2.3547 + } + ], - "cost_of_reading_ranges": 4.42, ++ "cost_of_reading_ranges": 2.3547, + "use_roworder_union": true, + "cause": "always cheaper than non roworder retrieval", + "analyzing_roworder_scans": [ + { + "type": "range_scan", + "index": "a", + "rows": 1, + "ranges": ["1 <= a <= 1"], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + } + }, + { + "type": "range_scan", + "index": "b", + "rows": 1, + "ranges": ["1 <= b <= 1"], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + } + } + ], - "index_roworder_union_cost": 6.2137, ++ "index_roworder_union_cost": 4.1484, + "members": 2, + "chosen": true + } + ] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "index_roworder_union", + "union_of": [ + { + "type": "range_scan", + "index": "a", + "rows": 1, + "ranges": ["1 <= a <= 1"] + }, + { + "type": "range_scan", + "index": "b", + "rows": 1, + "ranges": ["1 <= b <= 1"] + } + ] + }, + "rows_for_plan": 2, - "cost_for_plan": 6.2137, ++ "cost_for_plan": 4.1484, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [], + "selectivity_for_columns": [] + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "range", + "resulting_rows": 2, - "cost": 6.2137, ++ "cost": 4.1484, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = 1 or t1.b = 1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a = 1 or t1.b = 1" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + drop table t0,t1; + set optimizer_trace="enabled=off"; + set @@optimizer_switch= @tmp_opt_switch; diff --cc mysql-test/main/opt_trace_index_merge_innodb.result index 0000000,a0f03e3..43c9462 mode 000000,100644..100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@@ -1,0 -1,242 +1,248 @@@ ++set @innodb_stats_persistent_save= @@innodb_stats_persistent; ++set @innodb_stats_persistent_sample_pages_save= ++@@innodb_stats_persistent_sample_pages; ++set global innodb_stats_persistent= 1; ++set global innodb_stats_persistent_sample_pages=100; + create table t1 + ( + pk1 int not null, + pk2 int not null, + key1 int not null, + key2 int not null, + key (key1), + key (key2), + primary key (pk1, pk2) + )engine=Innodb; + analyze table t1; + Table Op Msg_type Msg_text + test.t1 analyze status Engine-independent statistics collected + test.t1 analyze status OK + set optimizer_trace="enabled=on"; + set @tmp_index_merge_ror_cpk=@@optimizer_switch; + set optimizer_switch='extended_keys=off'; + explain select * from t1 where pk1 != 0 and key1 = 1; + id select_type table type possible_keys key key_len ref rows Extra + 1 SIMPLE t1 ref PRIMARY,key1 key1 4 const 1 Using index condition + select * from information_schema.OPTIMIZER_TRACE; + QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES + explain select * from t1 where pk1 != 0 and key1 = 1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select `t1`.`pk1` AS `pk1`,`t1`.`pk2` AS `pk2`,`t1`.`key1` AS `key1`,`t1`.`key2` AS `key2` from `t1` where `t1`.`pk1` <> 0 and `t1`.`key1` = 1" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.pk1 <> 0 and t1.key1 = 1", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.pk1 <> 0 and multiple equal(1, t1.key1)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.pk1 <> 0 and multiple equal(1, t1.key1)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.pk1 <> 0 and multiple equal(1, t1.key1)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "key1", + "equals": "1", + "null_rejecting": false + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 206.1 + }, + "potential_range_indexes": [ + { + "index": "PRIMARY", + "usable": true, + "key_parts": ["pk1", "pk2"] + }, + { + "index": "key1", + "usable": true, + "key_parts": ["key1"] + }, + { + "index": "key2", + "usable": false, + "cause": "not applicable" + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "PRIMARY", + "ranges": ["pk1 < 0", "0 < pk1"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, - "rows": 1001, - "cost": 203.59, - "chosen": true ++ "rows": 1000, ++ "cost": 206.39, ++ "chosen": false, ++ "cause": "cost" + }, + { + "index": "key1", + "ranges": ["1 <= key1 <= 1"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, - "cost": 2.21, ++ "cost": 2.3751, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "intersecting_indexes": [ + { + "index": "key1", - "index_scan_cost": 1, - "cumulateed_index_scan_cost": 1, ++ "index_scan_cost": 1.0001, ++ "cumulateed_index_scan_cost": 1.0001, + "disk_sweep_cost": 1.0014, - "cumulative_total_cost": 2.0014, ++ "cumulative_total_cost": 2.0015, + "usable": true, + "matching_rows_now": 1, + "intersect_covering_with_this_index": false, + "chosen": true + } + ], + "clustered_pk": { - "index_scan_cost": 0.002, - "cumulateed_index_scan_cost": 1.002, - "disk_sweep_cost": 1.0014, + "clustered_pk_added_to_intersect": false, + "cause": "cost" + }, + "chosen": false, + "cause": "too few indexes to merge" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "key1", + "rows": 1, + "ranges": ["1 <= key1 <= 1"] + }, + "rows_for_plan": 1, - "cost_for_plan": 2.21, ++ "cost_for_plan": 2.3751, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "PRIMARY", - "selectivity_from_index": 1.001 ++ "selectivity_from_index": 1 + }, + { + "index_name": "key1", + "selectivity_from_index": 0.001 + } + ], + "selectivity_for_columns": [] + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "key1", + "used_range_estimates": true, + "rows": 1, + "cost": 2, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.key1 = 1 and t1.pk1 <> 0", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.pk1 <> 0" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] + } 0 0 + drop table t1; + set @@optimizer_switch= @tmp_index_merge_ror_cpk; + set optimizer_trace="enabled=off"; ++set global innodb_stats_persistent= @innodb_stats_persistent_save; ++set global innodb_stats_persistent_sample_pages= ++@innodb_stats_persistent_sample_pages_save; diff --cc mysql-test/main/opt_trace_index_merge_innodb.test index 0000000,5e18185..199c45b mode 000000,100644..100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.test +++ b/mysql-test/main/opt_trace_index_merge_innodb.test @@@ -1,0 -1,31 +1,45 @@@ + --source include/not_embedded.inc + --source include/have_innodb.inc ++ ++set @innodb_stats_persistent_save= @@innodb_stats_persistent; ++set @innodb_stats_persistent_sample_pages_save= ++ @@innodb_stats_persistent_sample_pages; ++ ++set global innodb_stats_persistent= 1; ++set global innodb_stats_persistent_sample_pages=100; ++ + create table t1 + ( + pk1 int not null, + pk2 int not null, + key1 int not null, + key2 int not null, + key (key1), + key (key2), + primary key (pk1, pk2) + )engine=Innodb; + + --disable_query_log + let $1=1000; + while ($1) + { + eval insert into t1 values (1+$1/10,$1 mod 100,$1,$1/100); + dec $1; + } + --enable_query_log + analyze table t1; + + set optimizer_trace="enabled=on"; + set @tmp_index_merge_ror_cpk=@@optimizer_switch; + set optimizer_switch='extended_keys=off'; + explain select * from t1 where pk1 != 0 and key1 = 1; + select * from information_schema.OPTIMIZER_TRACE; + drop table t1; + set @@optimizer_switch= @tmp_index_merge_ror_cpk; + set optimizer_trace="enabled=off"; ++ ++ ++set global innodb_stats_persistent= @innodb_stats_persistent_save; ++set global innodb_stats_persistent_sample_pages= ++ @innodb_stats_persistent_sample_pages_save; ++ diff --cc mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff index 3bb4494,4ec990d..36254fa --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff @@@ -694,7 -685,20 +694,20 @@@ VARIABLE_COMMENT Controls number of record samples to check condition selectivity NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 4294967295 - @@ -2762,7 +2762,7 @@ + @@ -2762,10 +2762,10 @@ + GLOBAL_VALUE_ORIGIN COMPILE-TIME + DEFAULT_VALUE 1048576 + VARIABLE_SCOPE SESSION + -VARIABLE_TYPE BIGINT UNSIGNED + +VARIABLE_TYPE INT UNSIGNED + VARIABLE_COMMENT Maximum allowed size of an optimizer trace + NUMERIC_MIN_VALUE 0 + -NUMERIC_MAX_VALUE 18446744073709551615 + +NUMERIC_MAX_VALUE 4294967295 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO -@@ -2776,7 +2776,7 @@ ++@@ -2790,7 +2790,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 4 VARIABLE_SCOPE SESSION @@@ -703,7 -707,7 +716,7 @@@ VARIABLE_COMMENT Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan or an index scan, 2 - use selectivity of index backed range conditions to calculate the cardinality of a partial join in any case, 3 - additionally always use selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join, 4 - use histograms to calculate selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join.5 - additionally use selectivity of certain non-range predicates calculated on record samples NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 5 - @@ -2790,7 +2790,7 @@ -@@ -2804,7 +2804,7 @@ ++@@ -2818,7 +2818,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -712,7 -716,7 +725,7 @@@ VARIABLE_COMMENT Maximum number of instrumented user@host accounts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -2804,7 +2804,7 @@ -@@ -2818,7 +2818,7 @@ ++@@ -2832,7 +2832,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -721,7 -725,7 +734,7 @@@ VARIABLE_COMMENT Size of the statement digest. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 200 - @@ -2818,7 +2818,7 @@ -@@ -2832,7 +2832,7 @@ ++@@ -2846,7 +2846,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -730,7 -734,7 +743,7 @@@ VARIABLE_COMMENT Number of rows in EVENTS_STAGES_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -2832,7 +2832,7 @@ -@@ -2846,7 +2846,7 @@ ++@@ -2860,7 +2860,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -739,7 -743,7 +752,7 @@@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STAGES_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 - @@ -2846,7 +2846,7 @@ -@@ -2860,7 +2860,7 @@ ++@@ -2874,7 +2874,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -748,7 -752,7 +761,7 @@@ VARIABLE_COMMENT Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -2860,7 +2860,7 @@ -@@ -2874,7 +2874,7 @@ ++@@ -2898,7 +2898,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -757,7 -761,7 +770,7 @@@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STATEMENTS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 - @@ -2874,7 +2874,7 @@ -@@ -2888,7 +2888,7 @@ ++@@ -2902,7 +2902,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -766,7 -770,7 +779,7 @@@ VARIABLE_COMMENT Number of rows in EVENTS_WAITS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -2888,7 +2888,7 @@ -@@ -2902,7 +2902,7 @@ ++@@ -2916,7 +2916,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -775,7 -779,7 +788,7 @@@ VARIABLE_COMMENT Number of rows per thread in EVENTS_WAITS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 - @@ -2902,7 +2902,7 @@ -@@ -2916,7 +2916,7 @@ ++@@ -2930,7 +2930,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -784,7 -788,7 +797,7 @@@ VARIABLE_COMMENT Maximum number of instrumented hosts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -2916,7 +2916,7 @@ -@@ -2930,7 +2930,7 @@ ++@@ -2944,7 +2944,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 80 VARIABLE_SCOPE GLOBAL @@@ -793,7 -797,7 +806,7 @@@ VARIABLE_COMMENT Maximum number of condition instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -2930,7 +2930,7 @@ -@@ -2944,7 +2944,7 @@ ++@@ -2958,7 +2958,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -802,7 -806,7 +815,7 @@@ VARIABLE_COMMENT Maximum number of instrumented condition objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -2944,7 +2944,7 @@ -@@ -2958,7 +2958,7 @@ ++@@ -2972,7 +2972,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 1024 VARIABLE_SCOPE GLOBAL @@@ -811,7 -815,7 +824,7 @@@ VARIABLE_COMMENT Maximum length considered for digest text, when stored in performance_schema tables. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 - @@ -2958,7 +2958,7 @@ -@@ -2972,7 +2972,7 @@ ++@@ -2986,7 +2986,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 50 VARIABLE_SCOPE GLOBAL @@@ -820,7 -824,7 +833,7 @@@ VARIABLE_COMMENT Maximum number of file instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -2972,7 +2972,7 @@ -@@ -2986,7 +2986,7 @@ ++@@ -3000,7 +3000,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 32768 VARIABLE_SCOPE GLOBAL @@@ -829,7 -833,7 +842,7 @@@ VARIABLE_COMMENT Maximum number of opened instrumented files. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 - @@ -2986,7 +2986,7 @@ -@@ -3000,7 +3000,7 @@ ++@@ -3014,7 +3014,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -838,7 -842,7 +851,7 @@@ VARIABLE_COMMENT Maximum number of instrumented files. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3000,7 +3000,7 @@ -@@ -3014,7 +3014,7 @@ ++@@ -3028,7 +3028,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 200 VARIABLE_SCOPE GLOBAL @@@ -847,7 -851,7 +860,7 @@@ VARIABLE_COMMENT Maximum number of mutex instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3014,7 +3014,7 @@ -@@ -3028,7 +3028,7 @@ ++@@ -3042,7 +3042,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -856,7 -860,7 +869,7 @@@ VARIABLE_COMMENT Maximum number of instrumented MUTEX objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 - @@ -3028,7 +3028,7 @@ -@@ -3042,7 +3042,7 @@ ++@@ -3056,7 +3056,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 40 VARIABLE_SCOPE GLOBAL @@@ -865,7 -869,7 +878,7 @@@ VARIABLE_COMMENT Maximum number of rwlock instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3042,7 +3042,7 @@ -@@ -3056,7 +3056,7 @@ ++@@ -3070,7 +3070,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -874,7 -878,7 +887,7 @@@ VARIABLE_COMMENT Maximum number of instrumented RWLOCK objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 - @@ -3056,7 +3056,7 @@ -@@ -3070,7 +3070,7 @@ ++@@ -3084,7 +3084,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 10 VARIABLE_SCOPE GLOBAL @@@ -883,7 -887,7 +896,7 @@@ VARIABLE_COMMENT Maximum number of socket instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3070,7 +3070,7 @@ -@@ -3084,7 +3084,7 @@ ++@@ -3098,7 +3098,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -892,7 -896,7 +905,7 @@@ VARIABLE_COMMENT Maximum number of opened instrumented sockets. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3084,7 +3084,7 @@ -@@ -3098,7 +3098,7 @@ ++@@ -3112,7 +3112,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 160 VARIABLE_SCOPE GLOBAL @@@ -901,7 -905,7 +914,7 @@@ VARIABLE_COMMENT Maximum number of stage instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3098,7 +3098,7 @@ -@@ -3112,7 +3112,7 @@ ++@@ -3126,7 +3126,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 202 VARIABLE_SCOPE GLOBAL @@@ -910,7 -914,7 +923,7 @@@ VARIABLE_COMMENT Maximum number of statement instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3112,7 +3112,7 @@ -@@ -3126,7 +3126,7 @@ ++@@ -3140,7 +3140,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -919,7 -923,7 +932,7 @@@ VARIABLE_COMMENT Maximum number of opened instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3126,7 +3126,7 @@ -@@ -3140,7 +3140,7 @@ ++@@ -3154,7 +3154,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -928,7 -932,7 +941,7 @@@ VARIABLE_COMMENT Maximum number of instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3140,7 +3140,7 @@ -@@ -3154,7 +3154,7 @@ ++@@ -3168,7 +3168,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 50 VARIABLE_SCOPE GLOBAL @@@ -937,7 -941,7 +950,7 @@@ VARIABLE_COMMENT Maximum number of thread instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3154,7 +3154,7 @@ -@@ -3168,7 +3168,7 @@ ++@@ -3182,7 +3182,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -946,7 -950,7 +959,7 @@@ VARIABLE_COMMENT Maximum number of instrumented threads. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3168,7 +3168,7 @@ -@@ -3182,7 +3182,7 @@ ++@@ -3196,7 +3196,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -955,7 -959,7 +968,7 @@@ VARIABLE_COMMENT Size of session attribute string buffer per thread. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3182,7 +3182,7 @@ -@@ -3196,7 +3196,7 @@ ++@@ -3210,7 +3210,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 100 VARIABLE_SCOPE GLOBAL @@@ -964,7 -968,7 +977,7 @@@ VARIABLE_COMMENT Maximum number of rows in SETUP_ACTORS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1024 - @@ -3196,7 +3196,7 @@ -@@ -3210,7 +3210,7 @@ ++@@ -3224,7 +3224,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 100 VARIABLE_SCOPE GLOBAL @@@ -973,7 -977,7 +986,7 @@@ VARIABLE_COMMENT Maximum number of rows in SETUP_OBJECTS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 - @@ -3210,7 +3210,7 @@ -@@ -3224,7 +3224,7 @@ ++@@ -3238,7 +3238,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -982,7 -986,7 +995,7 @@@ VARIABLE_COMMENT Maximum number of instrumented users. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3266,7 +3266,7 @@ -@@ -3280,7 +3280,7 @@ ++@@ -3294,7 +3294,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 32768 VARIABLE_SCOPE SESSION @@@ -991,7 -995,7 +1004,7 @@@ VARIABLE_COMMENT The size of the buffer that is allocated when preloading indexes NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 - @@ -3294,7 +3294,7 @@ -@@ -3308,7 +3308,7 @@ ++@@ -3322,7 +3322,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 15 VARIABLE_SCOPE SESSION @@@ -1000,7 -1004,7 +1013,7 @@@ VARIABLE_COMMENT Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 - @@ -3308,7 +3308,7 @@ -@@ -3322,7 +3322,7 @@ ++@@ -3336,7 +3336,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 5 VARIABLE_SCOPE SESSION @@@ -1009,7 -1013,7 +1022,7 @@@ VARIABLE_COMMENT Seconds between sending progress reports to the client for time-consuming statements. Set to 0 to disable progress reporting. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 - @@ -3392,7 +3392,7 @@ -@@ -3406,7 +3406,7 @@ ++@@ -3420,7 +3420,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 16384 VARIABLE_SCOPE SESSION @@@ -1018,7 -1022,7 +1031,7 @@@ VARIABLE_COMMENT Allocation block size for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 - @@ -3406,7 +3406,7 @@ -@@ -3420,7 +3420,7 @@ ++@@ -3434,7 +3434,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 1048576 VARIABLE_SCOPE GLOBAL @@@ -1027,7 -1031,7 +1040,7 @@@ VARIABLE_COMMENT Don't cache results that are bigger than this NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 - @@ -3420,7 +3420,7 @@ -@@ -3434,7 +3434,7 @@ ++@@ -3458,7 +3458,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 4096 VARIABLE_SCOPE GLOBAL @@@ -1036,7 -1040,7 +1049,7 @@@ VARIABLE_COMMENT The minimum size for blocks allocated by the query cache NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 - @@ -3437,7 +3437,7 @@ -@@ -3451,7 +3451,7 @@ ++@@ -3465,7 +3465,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The memory allocated to store results from old queries NUMERIC_MIN_VALUE 0 @@@ -1045,7 -1049,7 +1058,7 @@@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -3490,7 +3490,7 @@ -@@ -3504,7 +3504,7 @@ ++@@ -3518,7 +3518,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 24576 VARIABLE_SCOPE SESSION @@@ -1054,7 -1058,7 +1067,7 @@@ VARIABLE_COMMENT Persistent buffer for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 - @@ -3504,7 +3504,7 @@ -@@ -3518,7 +3518,7 @@ ++@@ -3532,7 +3532,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 4096 VARIABLE_SCOPE SESSION @@@ -1063,7 -1067,7 +1076,7 @@@ VARIABLE_COMMENT Allocation block size for storing ranges during optimization NUMERIC_MIN_VALUE 4096 NUMERIC_MAX_VALUE 4294967295 - @@ -3518,7 +3518,7 @@ -@@ -3532,7 +3532,7 @@ ++@@ -3546,7 +3546,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 131072 VARIABLE_SCOPE SESSION @@@ -1072,7 -1076,7 +1085,7 @@@ VARIABLE_COMMENT Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value NUMERIC_MIN_VALUE 8192 NUMERIC_MAX_VALUE 2147483647 - @@ -3546,7 +3546,7 @@ -@@ -3560,7 +3560,7 @@ ++@@ -3574,7 +3574,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 262144 VARIABLE_SCOPE SESSION @@@ -1081,7 -1085,7 +1094,7 @@@ VARIABLE_COMMENT When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 2147483647 - @@ -3560,10 +3560,10 @@ -@@ -3574,10 +3574,10 @@ ++@@ -3598,10 +3598,10 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 8388608 VARIABLE_SCOPE SESSION @@@ -1094,7 -1098,7 +1107,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -3616,7 +3616,7 @@ -@@ -3630,7 +3630,7 @@ ++@@ -3644,7 +3644,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE 1 VARIABLE_SCOPE SESSION @@@ -1103,7 -1107,7 +1116,7 @@@ VARIABLE_COMMENT Uniquely identifies the server instance in the community of replication partners NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 4294967295 - @@ -3714,7 +3714,7 @@ -@@ -3728,7 +3728,7 @@ ++@@ -3742,7 +3742,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 1073741824 VARIABLE_SCOPE GLOBAL @@@ -1112,7 -1116,7 +1125,7 @@@ VARIABLE_COMMENT The maximum packet length to sent successfully from the master to slave. NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 - @@ -3728,7 +3728,7 @@ -@@ -3742,7 +3742,7 @@ ++@@ -3756,7 +3756,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 2 VARIABLE_SCOPE GLOBAL @@@ -1121,7 -1125,7 +1134,7 @@@ VARIABLE_COMMENT If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 31536000 - @@ -3787,7 +3787,7 @@ -@@ -3801,7 +3801,7 @@ ++@@ -3715,7 +3715,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Each thread that needs to do a sort allocates a buffer of this size NUMERIC_MIN_VALUE 1024 @@@ -1130,7 -1134,7 +1143,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4078,7 +4078,7 @@ -@@ -4092,7 +4092,7 @@ ++@@ -4106,7 +4106,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 256 VARIABLE_SCOPE GLOBAL @@@ -1139,7 -1143,7 +1152,7 @@@ VARIABLE_COMMENT The soft upper limit for number of cached stored routines for one connection. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 524288 - @@ -4176,7 +4176,7 @@ -@@ -4190,7 +4190,7 @@ ++@@ -4204,7 +4204,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 400 VARIABLE_SCOPE GLOBAL @@@ -1148,7 -1152,7 +1161,7 @@@ VARIABLE_COMMENT The number of cached table definitions NUMERIC_MIN_VALUE 400 NUMERIC_MAX_VALUE 2097152 - @@ -4190,7 +4190,7 @@ -@@ -4204,7 +4204,7 @@ ++@@ -4218,7 +4218,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE 2000 VARIABLE_SCOPE GLOBAL @@@ -1157,7 -1161,7 +1170,7 @@@ VARIABLE_COMMENT The number of cached open tables NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 1048576 - @@ -4274,7 +4274,7 @@ -@@ -4288,7 +4288,7 @@ ++@@ -4302,7 +4302,7 @@ GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 256 VARIABLE_SCOPE GLOBAL @@@ -1166,7 -1170,7 +1179,7 @@@ VARIABLE_COMMENT How many threads we should keep in a cache for reuse. These are freed after 5 minutes of idle time NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16384 - @@ -4288,7 +4288,7 @@ -@@ -4302,7 +4302,7 @@ ++@@ -4316,7 +4316,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 10 VARIABLE_SCOPE GLOBAL @@@ -1175,7 -1179,7 +1188,7 @@@ VARIABLE_COMMENT Permits the application to give the threads system a hint for the desired number of threads that should be run at the same time.This variable has no effect, and is deprecated. It will be removed in a future release. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 512 - @@ -4395,15 +4395,15 @@ -@@ -4409,15 +4409,15 @@ ++@@ -4423,15 +4423,15 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TMP_DISK_TABLE_SIZE @@@ -1195,7 -1199,7 +1208,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4417,7 +4417,7 @@ -@@ -4431,7 +4431,7 @@ ++@@ -4445,7 +4445,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. Same as tmp_table_size. NUMERIC_MIN_VALUE 1024 @@@ -1204,7 -1208,7 +1217,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4431,7 +4431,7 @@ -@@ -4445,7 +4445,7 @@ ++@@ -4459,7 +4459,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Alias for tmp_memory_table_size. If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@@ -1213,7 -1217,7 +1226,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4442,7 +4442,7 @@ -@@ -4456,7 +4456,7 @@ ++@@ -4470,7 +4470,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 8192 VARIABLE_SCOPE SESSION @@@ -1222,7 -1226,7 +1235,7 @@@ VARIABLE_COMMENT Allocation block size for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 - @@ -4456,7 +4456,7 @@ -@@ -4470,7 +4470,7 @@ ++@@ -4484,7 +4484,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 4096 VARIABLE_SCOPE SESSION @@@ -1231,7 -1235,7 +1244,7 @@@ VARIABLE_COMMENT Persistent buffer for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 - @@ -4554,7 +4554,7 @@ -@@ -4568,7 +4568,7 @@ ++@@ -4582,7 +4582,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 28800 VARIABLE_SCOPE SESSION @@@ -1240,7 -1244,7 +1253,7 @@@ VARIABLE_COMMENT The number of seconds the server waits for activity on a connection before closing it NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 31536000 - @@ -4659,7 +4659,7 @@ -@@ -4673,7 +4673,7 @@ ++@@ -4687,7 +4687,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME OPEN_FILES_LIMIT VARIABLE_SCOPE GLOBAL @@@ -1249,7 -1253,7 +1262,7 @@@ VARIABLE_COMMENT If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 or autoset then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 - @@ -4672,7 +4672,7 @@ -@@ -4686,7 +4686,7 @@ ++@@ -4700,7 +4700,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@@ -1258,7 -1262,7 +1271,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4682,7 +4682,7 @@ -@@ -4696,7 +4696,7 @@ ++@@ -4710,7 +4710,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@@ -1267,7 -1271,7 +1280,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4777,7 +4777,7 @@ -@@ -4791,7 +4791,7 @@ ++@@ -4805,7 +4805,7 @@ VARIABLE_NAME LOG_TC_SIZE GLOBAL_VALUE_ORIGIN AUTO VARIABLE_SCOPE GLOBAL diff --cc mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index ddaa495,c896df1..628ba00 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@@ -2753,9 -2739,37 +2753,37 @@@ VARIABLE_COMMENT Fine-tune the optimize NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED + VARIABLE_NAME OPTIMIZER_TRACE + SESSION_VALUE enabled=off + GLOBAL_VALUE enabled=off + GLOBAL_VALUE_ORIGIN COMPILE-TIME + DEFAULT_VALUE enabled=off + VARIABLE_SCOPE SESSION + VARIABLE_TYPE FLAGSET + VARIABLE_COMMENT Controls tracing of the Optimizer: optimizer_trace=option=val[,option=val...], where option is one of {enabled} and val is one of {on, off, default} + NUMERIC_MIN_VALUE NULL + NUMERIC_MAX_VALUE NULL + NUMERIC_BLOCK_SIZE NULL + ENUM_VALUE_LIST enabled,default + READ_ONLY NO + COMMAND_LINE_ARGUMENT REQUIRED + VARIABLE_NAME OPTIMIZER_TRACE_MAX_MEM_SIZE + SESSION_VALUE 1048576 + GLOBAL_VALUE 1048576 + GLOBAL_VALUE_ORIGIN COMPILE-TIME + DEFAULT_VALUE 1048576 + VARIABLE_SCOPE SESSION + VARIABLE_TYPE BIGINT UNSIGNED + VARIABLE_COMMENT Maximum allowed size of an optimizer trace + NUMERIC_MIN_VALUE 0 + NUMERIC_MAX_VALUE 18446744073709551615 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO + COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_USE_CONDITION_SELECTIVITY SESSION_VALUE 4 GLOBAL_VALUE 4 diff --cc mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff index f51f1a1,53a74f9..13f9c78 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff @@@ -694,7 -685,20 +694,20 @@@ VARIABLE_COMMENT Controls number of record samples to check condition selectivity NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 4294967295 - @@ -2986,7 +2986,7 @@ + @@ -2986,10 +2986,10 @@ + GLOBAL_VALUE_ORIGIN COMPILE-TIME + DEFAULT_VALUE 1048576 + VARIABLE_SCOPE SESSION + -VARIABLE_TYPE BIGINT UNSIGNED + +VARIABLE_TYPE INT UNSIGNED + VARIABLE_COMMENT Maximum allowed size of an optimizer trace + NUMERIC_MIN_VALUE 0 + -NUMERIC_MAX_VALUE 18446744073709551615 + +NUMERIC_MAX_VALUE 4294967295 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO -@@ -3000,7 +3000,7 @@ ++@@ -2996,7 +2996,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 4 VARIABLE_SCOPE SESSION @@@ -703,7 -707,7 +716,7 @@@ VARIABLE_COMMENT Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan or an index scan, 2 - use selectivity of index backed range conditions to calculate the cardinality of a partial join in any case, 3 - additionally always use selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join, 4 - use histograms to calculate selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join.5 - additionally use selectivity of certain non-range predicates calculated on record samples NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 5 - @@ -3014,7 +3014,7 @@ -@@ -3028,7 +3028,7 @@ ++@@ -3042,7 +3042,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -712,7 -716,7 +725,7 @@@ VARIABLE_COMMENT Maximum number of instrumented user@host accounts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3028,7 +3028,7 @@ -@@ -3042,7 +3042,7 @@ ++@@ -3056,7 +3056,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -721,7 -725,7 +734,7 @@@ VARIABLE_COMMENT Size of the statement digest. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 200 - @@ -3042,7 +3042,7 @@ -@@ -3056,7 +3056,7 @@ ++@@ -3070,7 +3070,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -730,7 -734,7 +743,7 @@@ VARIABLE_COMMENT Number of rows in EVENTS_STAGES_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3056,7 +3056,7 @@ -@@ -3070,7 +3070,7 @@ ++@@ -3084,7 +3084,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -739,7 -743,7 +752,7 @@@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STAGES_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 - @@ -3070,7 +3070,7 @@ -@@ -3084,7 +3084,7 @@ ++@@ -3098,7 +3098,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -748,7 -752,7 +761,7 @@@ VARIABLE_COMMENT Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3084,7 +3084,7 @@ -@@ -3098,7 +3098,7 @@ ++@@ -3112,7 +3112,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -757,7 -761,7 +770,7 @@@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STATEMENTS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 - @@ -3098,7 +3098,7 @@ -@@ -3112,7 +3112,7 @@ ++@@ -3126,7 +3126,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -766,7 -770,7 +779,7 @@@ VARIABLE_COMMENT Number of rows in EVENTS_WAITS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3112,7 +3112,7 @@ -@@ -3126,7 +3126,7 @@ ++@@ -3140,7 +3140,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -775,7 -779,7 +788,7 @@@ VARIABLE_COMMENT Number of rows per thread in EVENTS_WAITS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 - @@ -3126,7 +3126,7 @@ -@@ -3140,7 +3140,7 @@ ++@@ -3154,7 +3154,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -784,7 -788,7 +797,7 @@@ VARIABLE_COMMENT Maximum number of instrumented hosts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3140,7 +3140,7 @@ -@@ -3154,7 +3154,7 @@ ++@@ -3168,7 +3168,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 80 VARIABLE_SCOPE GLOBAL @@@ -793,7 -797,7 +806,7 @@@ VARIABLE_COMMENT Maximum number of condition instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3154,7 +3154,7 @@ -@@ -3168,7 +3168,7 @@ ++@@ -3182,7 +3182,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -802,7 -806,7 +815,7 @@@ VARIABLE_COMMENT Maximum number of instrumented condition objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3168,7 +3168,7 @@ -@@ -3182,7 +3182,7 @@ ++@@ -3196,7 +3196,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 1024 VARIABLE_SCOPE GLOBAL @@@ -811,7 -815,7 +824,7 @@@ VARIABLE_COMMENT Maximum length considered for digest text, when stored in performance_schema tables. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 - @@ -3182,7 +3182,7 @@ -@@ -3196,7 +3196,7 @@ ++@@ -3210,7 +3210,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 50 VARIABLE_SCOPE GLOBAL @@@ -838,7 -842,7 +851,7 @@@ VARIABLE_COMMENT Maximum number of instrumented files. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3224,7 +3224,7 @@ -@@ -3238,7 +3238,7 @@ ++@@ -3252,7 +3252,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 200 VARIABLE_SCOPE GLOBAL @@@ -847,7 -851,7 +860,7 @@@ VARIABLE_COMMENT Maximum number of mutex instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3238,7 +3238,7 @@ -@@ -3252,7 +3252,7 @@ ++@@ -3266,7 +3266,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -856,7 -860,7 +869,7 @@@ VARIABLE_COMMENT Maximum number of instrumented MUTEX objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 - @@ -3252,7 +3252,7 @@ -@@ -3266,7 +3266,7 @@ ++@@ -3280,7 +3280,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 40 VARIABLE_SCOPE GLOBAL @@@ -865,7 -869,7 +878,7 @@@ VARIABLE_COMMENT Maximum number of rwlock instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3266,7 +3266,7 @@ -@@ -3280,7 +3280,7 @@ ++@@ -3294,7 +3294,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -874,7 -878,7 +887,7 @@@ VARIABLE_COMMENT Maximum number of instrumented RWLOCK objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 - @@ -3280,7 +3280,7 @@ -@@ -3294,7 +3294,7 @@ ++@@ -3308,7 +3308,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 10 VARIABLE_SCOPE GLOBAL @@@ -883,7 -887,7 +896,7 @@@ VARIABLE_COMMENT Maximum number of socket instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3294,7 +3294,7 @@ -@@ -3308,7 +3308,7 @@ ++@@ -3322,7 +3322,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -892,7 -896,7 +905,7 @@@ VARIABLE_COMMENT Maximum number of opened instrumented sockets. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3308,7 +3308,7 @@ -@@ -3322,7 +3322,7 @@ ++@@ -3336,7 +3336,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 160 VARIABLE_SCOPE GLOBAL @@@ -901,7 -905,7 +914,7 @@@ VARIABLE_COMMENT Maximum number of stage instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3322,7 +3322,7 @@ -@@ -3336,7 +3336,7 @@ ++@@ -3350,7 +3350,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 202 VARIABLE_SCOPE GLOBAL @@@ -910,7 -914,7 +923,7 @@@ VARIABLE_COMMENT Maximum number of statement instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3336,7 +3336,7 @@ -@@ -3350,7 +3350,7 @@ ++@@ -3364,7 +3364,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -919,7 -923,7 +932,7 @@@ VARIABLE_COMMENT Maximum number of opened instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3350,7 +3350,7 @@ -@@ -3364,7 +3364,7 @@ ++@@ -3378,7 +3378,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -928,7 -932,7 +941,7 @@@ VARIABLE_COMMENT Maximum number of instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3364,7 +3364,7 @@ -@@ -3378,7 +3378,7 @@ ++@@ -3392,7 +3392,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 50 VARIABLE_SCOPE GLOBAL @@@ -937,7 -941,7 +950,7 @@@ VARIABLE_COMMENT Maximum number of thread instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 - @@ -3378,7 +3378,7 @@ -@@ -3392,7 +3392,7 @@ ++@@ -3406,7 +3406,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -946,7 -950,7 +959,7 @@@ VARIABLE_COMMENT Maximum number of instrumented threads. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3392,7 +3392,7 @@ -@@ -3406,7 +3406,7 @@ ++@@ -3420,7 +3420,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -955,7 -959,7 +968,7 @@@ VARIABLE_COMMENT Size of session attribute string buffer per thread. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3406,7 +3406,7 @@ -@@ -3420,7 +3420,7 @@ ++@@ -3434,7 +3434,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 100 VARIABLE_SCOPE GLOBAL @@@ -964,7 -968,7 +977,7 @@@ VARIABLE_COMMENT Maximum number of rows in SETUP_ACTORS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1024 - @@ -3420,7 +3420,7 @@ -@@ -3434,7 +3434,7 @@ ++@@ -3448,7 +3448,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 100 VARIABLE_SCOPE GLOBAL @@@ -973,7 -977,7 +986,7 @@@ VARIABLE_COMMENT Maximum number of rows in SETUP_OBJECTS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 - @@ -3434,7 +3434,7 @@ -@@ -3448,7 +3448,7 @@ ++@@ -3462,7 +3462,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE -1 VARIABLE_SCOPE GLOBAL @@@ -982,7 -986,7 +995,7 @@@ VARIABLE_COMMENT Maximum number of instrumented users. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 - @@ -3490,7 +3490,7 @@ -@@ -3504,7 +3504,7 @@ ++@@ -3518,7 +3518,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 32768 VARIABLE_SCOPE SESSION @@@ -991,7 -995,7 +1004,7 @@@ VARIABLE_COMMENT The size of the buffer that is allocated when preloading indexes NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 - @@ -3518,7 +3518,7 @@ -@@ -3532,7 +3532,7 @@ ++@@ -3546,7 +3546,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 15 VARIABLE_SCOPE SESSION @@@ -1000,7 -1004,7 +1013,7 @@@ VARIABLE_COMMENT Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 - @@ -3532,7 +3532,7 @@ -@@ -3546,7 +3546,7 @@ ++@@ -3560,7 +3560,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 5 VARIABLE_SCOPE SESSION @@@ -1009,7 -1013,7 +1022,7 @@@ VARIABLE_COMMENT Seconds between sending progress reports to the client for time-consuming statements. Set to 0 to disable progress reporting. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 - @@ -3616,7 +3616,7 @@ -@@ -3630,7 +3630,7 @@ ++@@ -3644,7 +3644,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 16384 VARIABLE_SCOPE SESSION @@@ -1018,7 -1022,7 +1031,7 @@@ VARIABLE_COMMENT Allocation block size for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 - @@ -3630,7 +3630,7 @@ -@@ -3644,7 +3644,7 @@ ++@@ -3658,7 +3658,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 1048576 VARIABLE_SCOPE GLOBAL @@@ -1027,7 -1031,7 +1040,7 @@@ VARIABLE_COMMENT Don't cache results that are bigger than this NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 - @@ -3644,7 +3644,7 @@ -@@ -3658,7 +3658,7 @@ ++@@ -3672,7 +3672,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 4096 VARIABLE_SCOPE GLOBAL @@@ -1036,7 -1040,7 +1049,7 @@@ VARIABLE_COMMENT The minimum size for blocks allocated by the query cache NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 - @@ -3661,7 +3661,7 @@ -@@ -3675,7 +3675,7 @@ ++@@ -3689,7 +3689,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The memory allocated to store results from old queries NUMERIC_MIN_VALUE 0 @@@ -1045,7 -1049,7 +1058,7 @@@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -3714,7 +3714,7 @@ -@@ -3728,7 +3728,7 @@ ++@@ -3742,7 +3742,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 24576 VARIABLE_SCOPE SESSION @@@ -1054,7 -1058,7 +1067,7 @@@ VARIABLE_COMMENT Persistent buffer for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 - @@ -3728,7 +3728,7 @@ -@@ -3742,7 +3742,7 @@ ++@@ -3756,7 +3756,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 4096 VARIABLE_SCOPE SESSION @@@ -1063,7 -1067,7 +1076,7 @@@ VARIABLE_COMMENT Allocation block size for storing ranges during optimization NUMERIC_MIN_VALUE 4096 NUMERIC_MAX_VALUE 4294967295 - @@ -3745,7 +3745,7 @@ -@@ -3759,7 +3759,7 @@ ++@@ -3773,7 +3773,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Maximum speed(KB/s) to read binlog from master (0 = no limit) NUMERIC_MIN_VALUE 0 @@@ -1072,7 -1076,7 +1085,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -3756,7 +3756,7 @@ -@@ -3770,7 +3770,7 @@ ++@@ -3784,7 +3784,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 131072 VARIABLE_SCOPE SESSION @@@ -1081,7 -1085,7 +1094,7 @@@ VARIABLE_COMMENT Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value NUMERIC_MIN_VALUE 8192 NUMERIC_MAX_VALUE 2147483647 - @@ -3784,7 +3784,7 @@ -@@ -3798,7 +3798,7 @@ ++@@ -3812,7 +3812,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 262144 VARIABLE_SCOPE SESSION @@@ -1090,7 -1094,7 +1103,7 @@@ VARIABLE_COMMENT When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 2147483647 - @@ -4064,10 +4064,10 @@ -@@ -4078,10 +4078,10 @@ ++@@ -4092,10 +4092,10 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 8388608 VARIABLE_SCOPE SESSION @@@ -1103,7 -1107,7 +1116,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4092,10 +4092,10 @@ -@@ -4106,10 +4106,10 @@ ++@@ -4120,10 +4120,10 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 10000 VARIABLE_SCOPE GLOBAL @@@ -1116,7 -1120,7 +1129,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4106,10 +4106,10 @@ -@@ -4120,10 +4120,10 @@ ++@@ -4134,10 +4134,10 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 32 VARIABLE_SCOPE GLOBAL @@@ -1129,7 -1133,7 +1142,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4190,10 +4190,10 @@ -@@ -4204,10 +4204,10 @@ ++@@ -4218,10 +4218,10 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 32 VARIABLE_SCOPE GLOBAL @@@ -1142,7 -1146,7 +1155,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -4246,7 +4246,7 @@ -@@ -4260,7 +4260,7 @@ ++@@ -4274,7 +4274,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE 1 VARIABLE_SCOPE SESSION @@@ -1151,7 -1155,7 +1164,7 @@@ VARIABLE_COMMENT Uniquely identifies the server instance in the community of replication partners NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 4294967295 - @@ -4428,7 +4428,7 @@ -@@ -4442,7 +4442,7 @@ ++@@ -4456,7 +4456,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@@ -1160,7 -1164,7 +1173,7 @@@ VARIABLE_COMMENT Maximum number of parallel threads to use on slave for events in a single replication domain. When using multiple domains, this can be used to limit a single domain from grabbing all threads and thus stalling other domains. The default of 0 means to allow a domain to grab as many threads as it wants, up to the value of slave_parallel_threads. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16383 - @@ -4470,7 +4470,7 @@ -@@ -4484,7 +4484,7 @@ ++@@ -4498,7 +4498,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 1073741824 VARIABLE_SCOPE GLOBAL @@@ -1169,7 -1173,7 +1182,7 @@@ VARIABLE_COMMENT The maximum packet length to sent successfully from the master to slave. NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 - @@ -4498,7 +4498,7 @@ -@@ -4512,7 +4512,7 @@ ++@@ -4526,7 +4526,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 131072 VARIABLE_SCOPE GLOBAL @@@ -1178,7 -1182,7 +1191,7 @@@ VARIABLE_COMMENT Limit on how much memory SQL threads should use per parallel replication thread when reading ahead in the relay log looking for opportunities for parallel replication. Only used when --slave-parallel-threads > 0. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 2147483647 - @@ -4526,7 +4526,7 @@ -@@ -4540,7 +4540,7 @@ ++@@ -4554,7 +4554,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@@ -1187,7 -1191,7 +1200,7 @@@ VARIABLE_COMMENT If non-zero, number of threads to spawn to apply in parallel events on the slave that were group-committed on the master or were logged with GTID in different replication domains. Note that these threads are in addition to the IO and SQL threads, which are always created by a replication slave NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16383 - @@ -4540,7 +4540,7 @@ -@@ -4554,7 +4554,7 @@ ++@@ -4568,7 +4568,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@@ -1196,7 -1200,7 +1209,7 @@@ VARIABLE_COMMENT Alias for slave_parallel_threads NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16383 - @@ -4596,7 +4596,7 @@ -@@ -4610,7 +4610,7 @@ ++@@ -4624,7 +4624,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 10 VARIABLE_SCOPE GLOBAL @@@ -1205,7 -1209,7 +1218,7 @@@ VARIABLE_COMMENT Number of times the slave SQL thread will retry a transaction in case it failed with a deadlock, elapsed lock wait timeout or listed in slave_transaction_retry_errors, before giving up and stopping NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 - @@ -4624,7 +4624,7 @@ -@@ -4638,7 +4638,7 @@ ++@@ -4652,7 +4652,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@@ -1214,7 -1218,7 +1227,7 @@@ VARIABLE_COMMENT Interval of the slave SQL thread will retry a transaction in case it failed with a deadlock or elapsed lock wait timeout or listed in slave_transaction_retry_errors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 3600 - @@ -4652,7 +4652,7 @@ -@@ -4666,7 +4666,7 @@ ++@@ -4680,7 +4680,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 2 VARIABLE_SCOPE GLOBAL @@@ -1223,7 -1227,7 +1236,7 @@@ VARIABLE_COMMENT If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 31536000 - @@ -4711,7 +4711,7 @@ -@@ -4725,7 +4725,7 @@ ++@@ -4739,7 +4739,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Each thread that needs to do a sort allocates a buffer of this size NUMERIC_MIN_VALUE 1024 @@@ -1232,7 -1236,7 +1245,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -5016,7 +5016,7 @@ -@@ -5030,7 +5030,7 @@ ++@@ -5044,7 +5044,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 256 VARIABLE_SCOPE GLOBAL @@@ -1241,7 -1245,7 +1254,7 @@@ VARIABLE_COMMENT The soft upper limit for number of cached stored routines for one connection. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 524288 - @@ -5142,7 +5142,7 @@ -@@ -5156,7 +5156,7 @@ ++@@ -5170,7 +5170,7 @@ GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 400 VARIABLE_SCOPE GLOBAL @@@ -1250,7 -1254,7 +1263,7 @@@ VARIABLE_COMMENT The number of cached table definitions NUMERIC_MIN_VALUE 400 NUMERIC_MAX_VALUE 2097152 - @@ -5156,7 +5156,7 @@ -@@ -5170,7 +5170,7 @@ ++@@ -5184,7 +5184,7 @@ GLOBAL_VALUE_ORIGIN CONFIG DEFAULT_VALUE 2000 VARIABLE_SCOPE GLOBAL @@@ -1259,7 -1263,7 +1272,7 @@@ VARIABLE_COMMENT The number of cached open tables NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 1048576 - @@ -5240,7 +5240,7 @@ -@@ -5254,7 +5254,7 @@ ++@@ -5268,7 +5268,7 @@ GLOBAL_VALUE_ORIGIN AUTO DEFAULT_VALUE 256 VARIABLE_SCOPE GLOBAL @@@ -1268,7 -1272,7 +1281,7 @@@ VARIABLE_COMMENT How many threads we should keep in a cache for reuse. These are freed after 5 minutes of idle time NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16384 - @@ -5254,7 +5254,7 @@ -@@ -5268,7 +5268,7 @@ ++@@ -5282,7 +5282,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 10 VARIABLE_SCOPE GLOBAL @@@ -1277,7 -1281,7 +1290,7 @@@ VARIABLE_COMMENT Permits the application to give the threads system a hint for the desired number of threads that should be run at the same time.This variable has no effect, and is deprecated. It will be removed in a future release. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 512 - @@ -5459,15 +5459,15 @@ -@@ -5473,15 +5473,15 @@ ++@@ -5487,15 +5487,15 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TMP_DISK_TABLE_SIZE @@@ -1297,7 -1301,7 +1310,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -5481,7 +5481,7 @@ -@@ -5495,7 +5495,7 @@ ++@@ -5509,7 +5509,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. Same as tmp_table_size. NUMERIC_MIN_VALUE 1024 @@@ -1306,7 -1310,7 +1319,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -5495,7 +5495,7 @@ -@@ -5509,7 +5509,7 @@ ++@@ -5523,7 +5523,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Alias for tmp_memory_table_size. If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@@ -1315,7 -1319,7 +1328,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -5506,7 +5506,7 @@ -@@ -5520,7 +5520,7 @@ ++@@ -5534,7 +5534,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 8192 VARIABLE_SCOPE SESSION @@@ -1324,7 -1328,7 +1337,7 @@@ VARIABLE_COMMENT Allocation block size for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 - @@ -5520,7 +5520,7 @@ -@@ -5534,7 +5534,7 @@ ++@@ -5548,7 +5548,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 4096 VARIABLE_SCOPE SESSION @@@ -1333,7 -1337,7 +1346,7 @@@ VARIABLE_COMMENT Persistent buffer for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 - @@ -5618,7 +5618,7 @@ -@@ -5632,7 +5632,7 @@ ++@@ -5646,7 +5646,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 28800 VARIABLE_SCOPE SESSION @@@ -1342,7 -1346,7 +1355,7 @@@ VARIABLE_COMMENT The number of seconds the server waits for activity on a connection before closing it NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 31536000 - @@ -5723,7 +5723,7 @@ -@@ -5737,7 +5737,7 @@ ++@@ -5751,7 +5751,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME OPEN_FILES_LIMIT VARIABLE_SCOPE GLOBAL @@@ -1351,7 -1355,7 +1364,7 @@@ VARIABLE_COMMENT If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 or autoset then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 - @@ -5736,7 +5736,7 @@ -@@ -5750,7 +5750,7 @@ ++@@ -5764,7 +5764,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@@ -1360,7 -1364,7 +1373,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -5746,7 +5746,7 @@ -@@ -5760,7 +5760,7 @@ ++@@ -5774,7 +5774,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@@ -1369,7 -1373,7 +1382,7 @@@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO - @@ -5841,7 +5841,7 @@ -@@ -5855,7 +5855,7 @@ ++@@ -5869,7 +5869,7 @@ VARIABLE_NAME LOG_TC_SIZE GLOBAL_VALUE_ORIGIN AUTO VARIABLE_SCOPE GLOBAL diff --cc mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 6c57061,631b75b..da7790f --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@@ -2977,9 -2963,37 +2977,37 @@@ VARIABLE_COMMENT Fine-tune the optimize NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED + VARIABLE_NAME OPTIMIZER_TRACE + SESSION_VALUE enabled=off + GLOBAL_VALUE enabled=off + GLOBAL_VALUE_ORIGIN COMPILE-TIME + DEFAULT_VALUE enabled=off + VARIABLE_SCOPE SESSION + VARIABLE_TYPE FLAGSET + VARIABLE_COMMENT Controls tracing of the Optimizer: optimizer_trace=option=val[,option=val...], where option is one of {enabled} and val is one of {on, off, default} + NUMERIC_MIN_VALUE NULL + NUMERIC_MAX_VALUE NULL + NUMERIC_BLOCK_SIZE NULL + ENUM_VALUE_LIST enabled,default + READ_ONLY NO + COMMAND_LINE_ARGUMENT REQUIRED + VARIABLE_NAME OPTIMIZER_TRACE_MAX_MEM_SIZE + SESSION_VALUE 1048576 + GLOBAL_VALUE 1048576 + GLOBAL_VALUE_ORIGIN COMPILE-TIME + DEFAULT_VALUE 1048576 + VARIABLE_SCOPE SESSION + VARIABLE_TYPE BIGINT UNSIGNED + VARIABLE_COMMENT Maximum allowed size of an optimizer trace + NUMERIC_MIN_VALUE 0 + NUMERIC_MAX_VALUE 18446744073709551615 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO + COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_USE_CONDITION_SELECTIVITY SESSION_VALUE 4 GLOBAL_VALUE 4 diff --cc sql/CMakeLists.txt index 6cb78f4,0befcd2..ebc0579 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@@ -139,7 -139,7 +139,8 @@@ SET (SQL_SOURC sql_sequence.cc sql_sequence.h ha_sequence.h sql_tvc.cc sql_tvc.h opt_split.cc + rowid_filter.cc rowid_filter.h + opt_trace.cc ${WSREP_SOURCES} table_cache.cc encryption.cc temporary_tables.cc proxy_protocol.cc backup.cc diff --cc sql/opt_range.cc index c9dc56d,c87a059..468d16c --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@@ -2641,12 -2804,22 +2805,22 @@@ int SQL_SELECT::test_quick_select(THD * if (!force_quick_range && !head->covering_keys.is_clear_all()) { int key_for_use= find_shortest_key(head, &head->covering_keys); - double key_read_time= head->file->keyread_time(key_for_use, 1, records) + - (double) records / TIME_FOR_COMPARE; + double key_read_time= head->file->key_scan_time(key_for_use) + + (double) records / TIME_FOR_COMPARE_IDX; DBUG_PRINT("info", ("'all'+'using index' scan will be using key %d, " "read time %g", key_for_use, key_read_time)); + + Json_writer_object trace_cov(thd, "best_covering_index_scan"); + bool chosen= FALSE; if (key_read_time < read_time) + { read_time= key_read_time; + chosen= TRUE; + } + trace_cov.add("index", head->key_info[key_for_use].name) + .add("cost", key_read_time).add("chosen", chosen); + if (!chosen) + trace_cov.add("cause", "cost"); } TABLE_READ_PLAN *best_trp= NULL; @@@ -4791,8 -5015,8 +5016,9 @@@ TABLE_READ_PLAN *get_best_disjunct_quic double roru_index_costs; ha_rows roru_total_records; double roru_intersect_part= 1.0; + double limit_read_time= read_time; size_t n_child_scans; + THD *thd= param->thd; DBUG_ENTER("get_best_disjunct_quick"); DBUG_PRINT("info", ("Full table scan cost: %g", read_time)); @@@ -5399,22 -5673,38 +5675,38 @@@ bool prepare_search_best_index_intersec bzero(common->search_scans, sizeof(INDEX_SCAN_INFO *) * i); INDEX_SCAN_INFO **selected_index_scans= common->search_scans; - + Json_writer_array potential_idx_scans(thd, "potential_index_scans"); for (i=0, index_scan= tree->index_scans; i < n_index_scans; i++, index_scan++) { + Json_writer_object idx_scan(thd); uint used_key_parts= (*index_scan)->used_key_parts; KEY *key_info= (*index_scan)->key_info; + idx_scan.add("index", key_info->name); if (*index_scan == cpk_scan) + { + idx_scan.add("chosen", "false") + .add("cause", "clustered index used for filtering"); continue; + } if (cpk_scan && cpk_scan->used_key_parts >= used_key_parts && same_index_prefix(cpk_scan->key_info, key_info, used_key_parts)) + { + idx_scan.add("chosen", "false") + .add("cause", "clustered index used for filtering"); continue; + } - cost= table->file->keyread_time((*index_scan)->keynr, - (*index_scan)->range_count, - (*index_scan)->records); + cost= table->quick_index_only_costs[(*index_scan)->keynr]; + + idx_scan.add("cost", cost); ++ if (cost >= cutoff_cost) + { + idx_scan.add("chosen", false); + idx_scan.add("cause", "cost"); continue; + } for (scan_ptr= selected_index_scans; *scan_ptr ; scan_ptr++) { diff --cc sql/sql_select.cc index cec5ff7,1f12490..3670eff --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@@ -64,7 -64,9 +64,10 @@@ #include "sys_vars_shared.h" #include "sp_head.h" #include "sp_rcontext.h" +#include "rowid_filter.h" + #include "select_handler.h" + #include "my_json_writer.h" + #include "opt_trace.h" /* A key part number that means we're using a fulltext scan. @@@ -5081,146 -5064,160 +5184,163 @@@ make_join_statistics(JOIN *join, List<T /* Calc how many (possible) matched records in each table */ - for (s=stat ; s < stat_end ; s++) + /* + Todo: add a function so that we can add these Json_writer_objects + easily. + Another way would be to enclose them in a scope {}; + */ { - s->startup_cost= 0; - if (s->type == JT_SYSTEM || s->type == JT_CONST) - { - /* Only one matching row */ - s->found_records= s->records= 1; - s->read_time=1.0; - s->worst_seeks=1.0; - continue; - } - /* Approximate found rows and time to read them */ - if (s->table->is_filled_at_execution()) - { - get_delayed_table_estimates(s->table, &s->records, &s->read_time, - &s->startup_cost); - s->found_records= s->records; - table->quick_condition_rows=s->records; - } - else - { - s->scan_time(); - } + Json_writer_object rows_estimation_wrapper(thd); + Json_writer_array rows_estimation(thd, "rows_estimation"); + for (s=stat ; s < stat_end ; s++) + { + s->startup_cost= 0; + if (s->type == JT_SYSTEM || s->type == JT_CONST) + { + + Json_writer_object table_records(thd); + /* Only one matching row */ + s->found_records= s->records= 1; + s->read_time=1.0; + s->worst_seeks=1.0; + table_records.add_table_name(s) + .add("rows", s->found_records) + .add("cost", s->read_time) + .add("table_type", s->type == JT_CONST ? + "const" : + "system"); + continue; + } + /* Approximate found rows and time to read them */ + if (s->table->is_filled_at_execution()) + { + get_delayed_table_estimates(s->table, &s->records, &s->read_time, + &s->startup_cost); + s->found_records= s->records; + table->quick_condition_rows=s->records; + } + else + s->scan_time(); - if (s->table->is_splittable()) - s->add_keyuses_for_splitting(); + if (s->table->is_splittable()) + s->add_keyuses_for_splitting(); - /* - Set a max range of how many seeks we can expect when using keys - This is can't be to high as otherwise we are likely to use - table scan. - */ - s->worst_seeks= MY_MIN((double) s->found_records / 10, - (double) s->read_time*3); - if (s->worst_seeks < 2.0) // Fix for small tables - s->worst_seeks=2.0; + /* + Set a max range of how many seeks we can expect when using keys + This is can't be to high as otherwise we are likely to use + table scan. + */ + s->worst_seeks= MY_MIN((double) s->found_records / 10, + (double) s->read_time*3); + if (s->worst_seeks < 2.0) // Fix for small tables + s->worst_seeks=2.0; - /* - Add to stat->const_keys those indexes for which all group fields or - all select distinct fields participate in one index. - */ - add_group_and_distinct_keys(join, s); + /* + Add to stat->const_keys those indexes for which all group fields or + all select distinct fields participate in one index. + */ + add_group_and_distinct_keys(join, s); - s->table->cond_selectivity= 1.0; - - /* - Perform range analysis if there are keys it could use (1). - Don't do range analysis for materialized subqueries (2). - Don't do range analysis for materialized derived tables (3) - */ - if ((!s->const_keys.is_clear_all() || - !bitmap_is_clear_all(&s->table->cond_set)) && // (1) - !s->table->is_filled_at_execution() && // (2) - !(s->table->pos_in_table_list->derived && // (3) - s->table->pos_in_table_list->is_materialized_derived())) // (3) - { - bool impossible_range= FALSE; - ha_rows records= HA_POS_ERROR; - SQL_SELECT *select= 0; - Item **sargable_cond= NULL; - if (!s->const_keys.is_clear_all()) - { - sargable_cond= get_sargable_cond(join, s->table); - - select= make_select(s->table, found_const_table_map, - found_const_table_map, - *sargable_cond, - (SORT_INFO*) 0, - 1, &error); - if (!select) - goto error; - records= get_quick_record_count(join->thd, select, s->table, - &s->const_keys, join->row_limit); + s->table->cond_selectivity= 1.0; - /* - Range analyzer might have modified the condition. Put it the new - condition to where we got it from. - */ - *sargable_cond= select->cond; - - s->quick=select->quick; - s->needed_reg=select->needed_reg; - select->quick=0; - impossible_range= records == 0 && s->table->reginfo.impossible_range; - if (join->thd->lex->sql_command == SQLCOM_SELECT && - optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER)) - s->table->init_cost_info_for_usable_range_rowid_filters(join->thd); - } - if (!impossible_range) - { - if (!sargable_cond) + /* + Perform range analysis if there are keys it could use (1). + Don't do range analysis for materialized subqueries (2). + Don't do range analysis for materialized derived tables (3) + */ + if ((!s->const_keys.is_clear_all() || + !bitmap_is_clear_all(&s->table->cond_set)) && // (1) + !s->table->is_filled_at_execution() && // (2) + !(s->table->pos_in_table_list->derived && // (3) + s->table->pos_in_table_list->is_materialized_derived())) // (3) + { + bool impossible_range= FALSE; + ha_rows records= HA_POS_ERROR; + SQL_SELECT *select= 0; + Item **sargable_cond= NULL; + if (!s->const_keys.is_clear_all()) + { sargable_cond= get_sargable_cond(join, s->table); - if (join->thd->variables.optimizer_use_condition_selectivity > 1) - calculate_cond_selectivity_for_table(join->thd, s->table, - sargable_cond); - if (s->table->reginfo.impossible_range) - { - impossible_range= TRUE; - records= 0; + + select= make_select(s->table, found_const_table_map, + found_const_table_map, + *sargable_cond, + (SORT_INFO*) 0, 1, &error); + if (!select) + goto error; + records= get_quick_record_count(join->thd, select, s->table, - &s->const_keys, join->row_limit); ++ &s->const_keys, join->row_limit); + + /* + Range analyzer might have modified the condition. Put it the new + condition to where we got it from. + */ + *sargable_cond= select->cond; + + s->quick=select->quick; + s->needed_reg=select->needed_reg; + select->quick=0; + impossible_range= records == 0 && s->table->reginfo.impossible_range; ++ if (join->thd->lex->sql_command == SQLCOM_SELECT && ++ optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER)) ++ s->table->init_cost_info_for_usable_range_rowid_filters(join->thd); } - } - if (impossible_range) - { - /* - Impossible WHERE or ON expression - In case of ON, we mark that the we match one empty NULL row. - In case of WHERE, don't set found_const_table_map to get the - caller to abort with a zero row result. - */ - TABLE_LIST *emb= s->table->pos_in_table_list->embedding; - if (emb && !emb->sj_on_expr) + if (!impossible_range) { - /* Mark all tables in a multi-table join nest as const */ - mark_join_nest_as_const(join, emb, &found_const_table_map, - &const_count); + if (!sargable_cond) + sargable_cond= get_sargable_cond(join, s->table); + if (join->thd->variables.optimizer_use_condition_selectivity > 1) + calculate_cond_selectivity_for_table(join->thd, s->table, + sargable_cond); + if (s->table->reginfo.impossible_range) + { + impossible_range= TRUE; + records= 0; + } } - else + if (impossible_range) { - join->const_table_map|= s->table->map; - set_position(join,const_count++,s,(KEYUSE*) 0); - s->type= JT_CONST; - s->table->const_table= 1; - if (*s->on_expr_ref) + /* + Impossible WHERE or ON expression + In case of ON, we mark that the we match one empty NULL row. + In case of WHERE, don't set found_const_table_map to get the + caller to abort with a zero row result. + */ + TABLE_LIST *emb= s->table->pos_in_table_list->embedding; + if (emb && !emb->sj_on_expr) + { + /* Mark all tables in a multi-table join nest as const */ + mark_join_nest_as_const(join, emb, &found_const_table_map, + &const_count); + } + else { - /* Generate empty row */ - s->info= ET_IMPOSSIBLE_ON_CONDITION; - found_const_table_map|= s->table->map; - mark_as_null_row(s->table); // All fields are NULL + join->const_table_map|= s->table->map; + set_position(join,const_count++,s,(KEYUSE*) 0); + s->type= JT_CONST; + s->table->const_table= 1; + if (*s->on_expr_ref) + { + /* Generate empty row */ + s->info= ET_IMPOSSIBLE_ON_CONDITION; + found_const_table_map|= s->table->map; + mark_as_null_row(s->table); // All fields are NULL + } } } + if (records != HA_POS_ERROR) + { + s->found_records=records; + s->read_time= s->quick ? s->quick->read_time : 0.0; + } + if (select) + delete select; + else + add_table_scan_values_to_trace(thd, s); } - if (records != HA_POS_ERROR) - { - s->found_records=records; - s->read_time= s->quick ? s->quick->read_time : 0.0; - } - if (select) - delete select; + else + add_table_scan_values_to_trace(thd, s); } - } if (pull_out_semijoin_tables(join)) @@@ -6980,8 -6976,7 +7100,8 @@@ best_access_path(JOIN *join MY_BITMAP *eq_join_set= &s->table->eq_join_set; KEYUSE *hj_start_key= 0; SplM_plan_info *spl_plan= 0; + Range_rowid_filter_cost_info *filter= 0; - double filter_cmp_gain= 0; + const char* cause= NULL; disable_jbuf= disable_jbuf || idx == join->const_tables; @@@ -7374,20 -7419,7 +7545,21 @@@ loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp); } /* not ft_key */ + if (records < DBL_MAX) + { + double rows= record_count * records; + double access_cost_factor= MY_MIN(tmp / rows, 1.0); + filter= + table->best_range_rowid_filter_for_partial_join(start_key->key, rows, + access_cost_factor); + if (filter) + { + filter->get_cmp_gain(rows); + tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows); + DBUG_ASSERT(tmp >= 0); + } + } + trace_access_idx.add("rows", records).add("cost", tmp); if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE) { @@@ -7397,8 -7430,13 +7570,14 @@@ best_key= start_key; best_max_key_part= max_key_part; best_ref_depends_map= found_ref; + best_filter= filter; } + else + { + trace_access_idx.add("chosen", false) + .add("cause", cause ? cause : "cost"); + } + cause= NULL; } /* for each key */ records= best_records; } @@@ -7439,8 -7478,12 +7619,13 @@@ best_key= hj_start_key; best_ref_depends_map= 0; best_uses_jbuf= TRUE; + best_filter= 0; - } + trace_access_hash.add("type", "hash"); + trace_access_hash.add("index", "hj-key"); + trace_access_hash.add("cost", rnd_records); + trace_access_hash.add("cost", best); + trace_access_hash.add("chosen", true); + } /* Don't test table scan if it can't be better. @@@ -7492,9 -7536,12 +7678,13 @@@ Here we estimate its cost. */ + filter= 0; if (s->quick) { + trace_access_scan.add("access_type", "range"); + /* + should have some info about all the different QUICK_SELECT + */ /* For each record we: - read record range through 'quick' @@@ -7572,13 -7604,8 +7763,15 @@@ as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus tmp give us total cost of using TABLE SCAN */ + + double best_filter_cmp_gain= 0; + if (best_filter) + { + best_filter_cmp_gain= best_filter->get_cmp_gain(record_count * records); + } + trace_access_scan.add("resulting_rows", rnd_records); + trace_access_scan.add("cost", tmp); + if (best == DBL_MAX || (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records < (best_key->is_for_hash_join() ? best_time : @@@ -7592,13 -7618,6 +7785,9 @@@ best= tmp; records= rnd_records; best_key= 0; + best_filter= 0; - if (s->quick && s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE && - filter) - { ++ if (s->quick && s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE) + best_filter= filter; - best_filter_cmp_gain= filter_cmp_gain; - } /* range/index_merge/ALL/index access method are "independent", so: */ best_ref_depends_map= 0; best_uses_jbuf= MY_TEST(!disable_jbuf && !((s->table->map & @@@ -8099,22 -8132,20 +8303,28 @@@ optimize_straight_join(JOIN *join, tabl for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++) { + POSITION *position= join->positions + idx; - /* Find the best access method from 's' to the current partial plan */ + Json_writer_object trace_one_table(thd); + if (unlikely(thd->trace_started())) + { + trace_plan_prefix(join, idx, join_tables); + trace_one_table.add_table_name(s); + } + /* Find the best access method from 's' to the current partial plan */ best_access_path(join, s, join_tables, idx, disable_jbuf, record_count, - join->positions + idx, &loose_scan_pos); + position, &loose_scan_pos); /* compute the cost of the new plan extended with 's' */ - record_count*= join->positions[idx].records_read; - read_time+= join->positions[idx].read_time + - record_count / (double) TIME_FOR_COMPARE; + record_count*= position->records_read; + double filter_cmp_gain= 0; + if (position->range_rowid_filter_info) + { + filter_cmp_gain= + position->range_rowid_filter_info->get_cmp_gain(record_count); + } + read_time+= position->read_time + + record_count / (double) TIME_FOR_COMPARE - + filter_cmp_gain; advance_sj_state(join, join_tables, idx, &record_count, &read_time, &loose_scan_pos); @@@ -9022,16 -9072,12 +9251,19 @@@ best_extension_by_limited_search(JOI current_record_count= record_count * position->records_read; else current_record_count= DBL_MAX; + double filter_cmp_gain= 0; + if (position->range_rowid_filter_info) + { + filter_cmp_gain= + position->range_rowid_filter_info->get_cmp_gain(current_record_count); + } current_read_time=read_time + position->read_time + - current_record_count / (double) TIME_FOR_COMPARE; + current_record_count / (double) TIME_FOR_COMPARE - + filter_cmp_gain; + /* + TODO add filtering estimates here + */ advance_sj_state(join, remaining_tables, idx, ¤t_record_count, ¤t_read_time, &loose_scan_pos); diff --cc sql/table.h index af38082,40dd752..914f4dc --- a/sql/table.h +++ b/sql/table.h @@@ -55,7 -55,8 +55,9 @@@ class Virtual_column_info class Table_triggers_list; class TMP_TABLE_PARAM; class SEQUENCE; +class Range_rowid_filter_cost_info; + class derived_handler; + class Pushdown_derived; /* Used to identify NESTED_JOIN structures within a join (applicable only to