MariaDB and MySQL work

/ home / computer / .

MariaDB / MySQL - Optimizer

Analyze

• analyze_sample_percentage
• use_stat_tables

Optimize

• in_predicate_conversion_threshold
• max_rowid_filter_size
• max_seeks_for_key
• optimizer_adjust_secondary_key_costs
• optimizer_extra_pruning_depth
• optimizer_join_limit_pref_ratio
• optimizer_max_sel_arg_weight
• optimizer_max_sel_args
• optimizer_prune_level
• optimizer_search_depth
• optimizer_selectivity_sampling_limit
• optimizer_switch
• optimizer_trace
• optimizer_trace_max_mem_size
• optimizer_use_condition_selectivity

Execute

• big_tables
• expensive_subquery_limit
• max_recursive_iterations
• progress_report_time
• query_alloc_block_size
• query_prealloc_size
• range_alloc_block_size
• read_buffer_size
• read_rnd_buffer_size
• rowid_merge_buff_size
• sort_buffer_size
• max_join_size
• sql_big_tables
• sql_big_selects
• sql_buffer_result
• sql_select_limit
• tmp_table_size
• transaction_alloc_block_size
• transaction_prealloc_size

Optimizer tests

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  i_ui INT UNSIGNED NULL,
  i_ni INT UNSIGNED NULL,
  dt_i DATETIME,
  dt_ni DATETIME,
  s_i VARCHAR(255),
  s_ni VARCHAR(255),
  PRIMARY KEY (`id`),
  UNIQUE INDEX (i_ui),
  INDEX (dt_i),
  INDEX (s_i)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
;

INSERT INTO test SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM test;
-- 1M rows

SELECT MAX(id) INTO @max FROM test;
UPDATE test SET i_ui = @max - id, i_ni  = @max, dt_i = DATE_ADD(NOW(), INTERVAL id SECOND), dt_ni = DATE_ADD(NOW(), INTERVAL id SECOND), s_i = CONCAT('Some text ', id), s_ni = CONCAT('Some text ', id);

-- Primary Key access
SQL> EXPLAIN SELECT * FROM test WHERE id = 42;
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

-- Unique Key access
SQL> EXPLAIN SELECT * FROM test WHERE i_ui = 42;
+------+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+-------+------+-------+
|    1 | SIMPLE      | test  | const | i_ui          | i_ui | 5       | const | 1    |       |
+------+-------------+-------+-------+---------------+------+---------+-------+------+-------+

-- Secondary Key access
SQL> EXPLAIN SELECT * FROM test WHERE s_i = 'Some text 1000042';
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | test  | ref  | s_i           | s_i  | 1023    | const | 1    | Using index condition |
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

-- Non indexed column access
SQL> EXPLAIN SELECT * FROM test WHERE i_ni = 42;
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 1063513 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+

-- Secondary index range access
SQL> EXPLAIN SELECT * FROM test WHERE i_ui BETWEEN 10 AND 42;
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | test  | range | i_ui          | i_ui | 5       | NULL | 33   | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

-- Secondary index LIKE access
SQL> EXPLAIN SELECT * FROM test WHERE s_i LIKE 'Some text 10000%';
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | test  | range | s_i           | s_i  | 1023    | NULL | 101  | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

-- Index full scan on Primary Key
SQL> EXPLAIN SELECT id FROM test;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | test  | index | NULL          | i_ui | 5       | NULL | 1063513 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
-- It does a index full scan on a secondary key!

-- Index range scan on Primary Key
SQL> EXPLAIN SELECT id FROM test WHERE id between 100 and 1000;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 4       | NULL | 448  | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
-- It does a range scan on the Primary Key


-- Forcing a range scan on secondary key
SQL> EXPLAIN SELECT id FROM test USE INDEX (i_ui) WHERE id between 100 and 1000;
+------+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+------+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
|    1 | SIMPLE      | test  | index | NULL          | i_ui | 5       | NULL | 1063513 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
-- Somehow it seems not capable to short cut here!!!

-- Index full scan on Secondary Key
SQL> EXPLAIN SELECT i_ui FROM test;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | test  | index | NULL          | i_ui | 5       | NULL | 1063513 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+


-- Index range scan on Secondary Key
SQL> EXPLAIN SELECT i_ui FROM test WHERE i_ui between 100 and 1000;
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|    1 | SIMPLE      | test  | range | i_ui          | i_ui | 5       | NULL | 901  | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

-- Index range scan on a covering index
SQL> EXPLAIN SELECT id from test WHERE i_ui between 100 and 1000;
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|    1 | SIMPLE      | test  | range | i_ui          | i_ui | 5       | NULL | 901  | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+