database/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-08-01 03:47:19 +03:00
Code Activity
Files
main
mariadb/mysql-test/suite/sys_vars/t/optimizer_prune_level_basic.test
Monty 515b9ad05a Added EQ_REF chaining to the greedy_optimizer 
MDEV-28073 Slow query performance in MariaDB when using many table

The idea is to prefer and chain EQ_REF tables (tables that uses an
unique key to find a row) when searching for the best table combination.
This significantly reduces row combinations that has to be examined.
This is optimization is enabled when setting optimizer_prune_level=2
(which is now default).

Implementation:
- optimizer_prune_level has a new level, 2, which enables EQ_REF
  optimization in addition to the pruning done by level 1.
  Level 2 is now default.
- Added JOIN::eq_ref_tables that contains bits of tables that could use
  potentially use EQ_REF access in the query.  This is calculated
  in sort_and_filter_keyuse()

Under optimizer_prune_level=2:
- When the greedy_optimizer notices that the preceding table was an
  EQ_REF table, it tries to add an EQ_REF table next. If an EQ_REF
  table exists, only this one will be considered at this level.
  We also collect all EQ_REF tables chained by the next levels and these
  are ignored on the starting level as we have already examined these.
  If no EQ_REF table exists, we continue as normal.

This optimization speeds up the greedy_optimizer combination test with
~25%

Other things:
- I ported the changes in MySQL 5.7 to greedy_optimizer.test to MariaDB
  to be able to ensure we can handle all cases that MySQL can do.
- I have run all tests with --mysqld=--optimizer_prune_level=1 to verify that
  there where no test changes.
2022-07-26 22:27:29 +07:00

234 lines
9.8 KiB
Plaintext
Raw Permalink Blame History

############## mysql-test\t\optimizer_prune_level_basic.test ##################
# #
# Variable Name: optimizer_prune_level #
# Scope: GLOBAL | SESSION #
# Access Type: Dynamic #
# Data Type: boolean #
# Default Value: 1 #
# Range: #
# #
# #
# Creation Date: 2008-02-14 #
# Author: Salman #
# #
# Description: Test Cases of Dynamic System Variable optimizer_prune_level #
# that checks the behavior of this variable in the following ways#
# * Default Value #
# * Valid & Invalid values #
# * Scope & Access method #
# * Data Integrity #
# #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
# server-system-variables.html#option_mysqld_optimizer_prune_level #
# #
###############################################################################
--source include/load_sysvars.inc
###################################################
# START OF optimizer_prune_level TESTS #
###################################################
#############################################################
# Save initial value #
#############################################################
SET @start_global_value = @@global.optimizer_prune_level;
SELECT @start_global_value;
SET @start_session_value = @@session.optimizer_prune_level;
SELECT @start_session_value;
--echo '#--------------------FN_DYNVARS_115_01-------------------------#'
###########################################################
# Display the DEFAULT value of optimizer_prune_level #
###########################################################
SET @@global.optimizer_prune_level = 0;
SET @@global.optimizer_prune_level = DEFAULT;
SELECT @@global.optimizer_prune_level;
SET @@session.optimizer_prune_level = 0;
SET @@session.optimizer_prune_level = DEFAULT;
SELECT @@session.optimizer_prune_level;
--echo '#--------------------FN_DYNVARS_115_02-------------------------#'
###########################################################
# Check the DEFAULT value of optimizer_prune_level #
###########################################################
SET @@global.optimizer_prune_level = DEFAULT;
SELECT @@global.optimizer_prune_level = 2;
SET @@session.optimizer_prune_level = DEFAULT;
SELECT @@session.optimizer_prune_level = 2;
--echo '#--------------------FN_DYNVARS_115_03-------------------------#'
###############################################################################
# Change the value of optimizer_prune_level to a valid value for GLOBAL Scope #
###############################################################################
SELECT @@global.optimizer_prune_level;
SET @@global.optimizer_prune_level = 0;
SELECT @@global.optimizer_prune_level;
SET @@global.optimizer_prune_level = 1;
SELECT @@global.optimizer_prune_level;
SET @@global.optimizer_prune_level = 2;
SELECT @@global.optimizer_prune_level;
SET @@global.optimizer_prune_level = TRUE;
SELECT @@global.optimizer_prune_level;
SET @@global.optimizer_prune_level = FALSE;
SELECT @@global.optimizer_prune_level;
--echo '#--------------------FN_DYNVARS_115_04-------------------------#'
###############################################################################
# Change the value of optimizer_prune_level to a valid value for SESSION Scope#
###############################################################################
SELECT @@session.optimizer_prune_level;
SET @@session.optimizer_prune_level = 0;
SELECT @@session.optimizer_prune_level;
SET @@session.optimizer_prune_level = 1;
SELECT @@session.optimizer_prune_level;
SET @@session.optimizer_prune_level = 2;
SELECT @@session.optimizer_prune_level;
SET @@session.optimizer_prune_level = TRUE;
SELECT @@session.optimizer_prune_level;
SET @@session.optimizer_prune_level = FALSE;
SELECT @@session.optimizer_prune_level;
--echo '#------------------FN_DYNVARS_115_05-----------------------#'
#################################################################
# Change the value of optimizer_prune_level to an invalid value #
#################################################################
--Error ER_WRONG_TYPE_FOR_VAR
SET @@global.optimizer_prune_level = ON;
--echo 'Bug# 34840: Since it is not a boolean variable, it should give errors on 'ON' & 'OFF' values';
--Error ER_WRONG_TYPE_FOR_VAR
SET @@global.optimizer_prune_level = OFF;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@global.optimizer_prune_level = 'ONN';
--Error ER_WRONG_TYPE_FOR_VAR
SET @@global.optimizer_prune_level = "OFFF";
--Error ER_WRONG_TYPE_FOR_VAR
SET @@global.optimizer_prune_level = TTRUE;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@global.optimizer_prune_level = FELSE;
SET @@global.optimizer_prune_level = -1024;
SELECT @@global.optimizer_prune_level;
--echo 'Bug# 34840: Since it is not a boolean variable, it should no give errors on numeric values';
SET @@global.optimizer_prune_level = 65536;
SET @@global.optimizer_prune_level = 3;
select @@global.optimizer_prune_level;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@global.optimizer_prune_level = 65530.34;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@global.optimizer_prune_level = test;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@session.optimizer_prune_level = ON;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@session.optimizer_prune_level = OFF;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@session.optimizer_prune_level = ONN;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@session.optimizer_prune_level = ONF;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@session.optimizer_prune_level = ON;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@session.optimizer_prune_level = OF;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@session.optimizer_prune_level = 'OFN';
SET @@session.optimizer_prune_level = -2;
SELECT @@session.optimizer_prune_level;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@session.optimizer_prune_level = 65530.34;
SET @@session.optimizer_prune_level = 65550;
SELECT @@session.optimizer_prune_level;
--Error ER_WRONG_TYPE_FOR_VAR
SET @@session.optimizer_prune_level = test;
--echo '#------------------FN_DYNVARS_115_06-----------------------#'
####################################################################
# Check if the value in GLOBAL Table matches value in variable #
####################################################################
SELECT @@global.optimizer_prune_level = VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
WHERE VARIABLE_NAME='optimizer_prune_level';
--echo '#------------------FN_DYNVARS_115_07-----------------------#'
####################################################################
# Check if the value in SESSION Table matches value in variable #
####################################################################
SELECT @@session.optimizer_prune_level = VARIABLE_VALUE
FROM INFORMATION_SCHEMA.SESSION_VARIABLES
WHERE VARIABLE_NAME='optimizer_prune_level';
--echo '#---------------------FN_DYNVARS_115_08----------------------#'
###############################################################################
# Check if global and session variable are independent of each other #
###############################################################################
SET @@optimizer_prune_level = 0;
SET @@global.optimizer_prune_level = 1;
SELECT @@optimizer_prune_level = @@global.optimizer_prune_level;
--echo '#---------------------FN_DYNVARS_115_09----------------------#'
###############################################################################
# Check if accessing variable with SESSION,LOCAL and without SCOPE points #
# to same session variable #
###############################################################################
SET @@optimizer_prune_level = 1;
SELECT @@optimizer_prune_level = @@local.optimizer_prune_level;
SELECT @@local.optimizer_prune_level = @@session.optimizer_prune_level;
--echo '#---------------------FN_DYNVARS_115_10----------------------#'
###############################################################################
# Check if optimizer_prune_level can be accessed with and without @@ sign #
###############################################################################
SET optimizer_prune_level = 1;
SELECT @@optimizer_prune_level;
--Error ER_UNKNOWN_TABLE
SELECT local.optimizer_prune_level;
--Error ER_UNKNOWN_TABLE
SELECT session.optimizer_prune_level;
--Error ER_BAD_FIELD_ERROR
SELECT optimizer_prune_level = @@session.optimizer_prune_level;
####################################
# Restore initial value #
####################################
SET @@global.optimizer_prune_level = @start_global_value;
SELECT @@global.optimizer_prune_level;
SET @@session.optimizer_prune_level = @start_session_value;
SELECT @@session.optimizer_prune_level;
##########################################################
# END OF optimizer_prune_level TESTS #
##########################################################
View Git Blame Copy Permalink