mirror of
https://github.com/MariaDB/server.git
synced 2025-02-12 00:15:35 +01:00
727491b72a
This includes all test changes from
"Changing all cost calculation to be given in milliseconds"
and forwards.
Some of the things that caused changes in the result files:
- As part of fixing tests, I added 'echo' to some comments to be able to
easier find out where things where wrong.
- MATERIALIZED has now a higher cost compared to X than before. Because
of this some MATERIALIZED types have changed to DEPENDEND SUBQUERY.
- Some test cases that required MATERIALIZED to repeat a bug was
changed by adding more rows to force MATERIALIZED to happen.
- 'Filtered' in SHOW EXPLAIN has in many case changed from 100.00 to
something smaller. This is because now filtered also takes into
account the smallest possible ref access and filters, even if they
where not used. Another reason for 'Filtered' being smaller is that
we now also take into account implicit filtering done for subqueries
using FIRSTMATCH.
(main.subselect_no_exists_to_in)
This is caluculated in best_access_path() and stored in records_out.
- Table orders has changed because more accurate costs.
- 'index' and 'ALL' for small tables has changed to use 'range' or
'ref' because of optimizer_scan_setup_cost.
- index can be changed to 'range' as 'range' optimizer assumes we don't
have to read the blocks from disk that range optimizer has already read.
This can be confusing in the case where there is no obvious where clause
but instead there is a hidden 'key_column > NULL' added by the optimizer.
(main.subselect_no_exists_to_in)
- Scan on primary clustered key does not report 'Using Index' anymore
(It's a table scan, not an index scan).
- For derived tables, the number of rows is now 100 instead of 2,
which can be seen in EXPLAIN.
- More tests have "Using index for group by" as the cost of this
optimization is now more correct (lower).
- A primary key could be preferred for a normal key, even if it would
access more rows, as it's faster to do 1 lokoup and 3 'index_next' on a
clustered primary key than one lookup trough a secondary.
(main.stat_tables_innodb)
Notes:
- There was a 4.7% more calls to best_extension_by_limited_search() in
the main.greedy_optimizer test. However examining the test results
it looked that the plans where slightly better (eq_ref where more
chained together) so I assume this is ok.
- I have verified a few test cases where there was notable/unexpected
changes in the plan and in all cases the new optimizer plans where
faster. (main.greedy_optimizer and some others)
137 lines
4.3 KiB
Text
137 lines
4.3 KiB
Text
connection node_2;
|
|
connection node_1;
|
|
CREATE TABLE t1 (f1 VARCHAR(767) PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE UNIQUE INDEX i63 ON t1(f1);
|
|
CREATE UNIQUE INDEX i62 ON t1(f1);
|
|
CREATE UNIQUE INDEX i61 ON t1(f1);
|
|
CREATE UNIQUE INDEX i60 ON t1(f1);
|
|
CREATE UNIQUE INDEX i59 ON t1(f1);
|
|
CREATE UNIQUE INDEX i58 ON t1(f1);
|
|
CREATE UNIQUE INDEX i57 ON t1(f1);
|
|
CREATE UNIQUE INDEX i56 ON t1(f1);
|
|
CREATE UNIQUE INDEX i55 ON t1(f1);
|
|
CREATE UNIQUE INDEX i54 ON t1(f1);
|
|
CREATE UNIQUE INDEX i53 ON t1(f1);
|
|
CREATE UNIQUE INDEX i52 ON t1(f1);
|
|
CREATE UNIQUE INDEX i51 ON t1(f1);
|
|
CREATE UNIQUE INDEX i50 ON t1(f1);
|
|
CREATE UNIQUE INDEX i49 ON t1(f1);
|
|
CREATE UNIQUE INDEX i48 ON t1(f1);
|
|
CREATE UNIQUE INDEX i47 ON t1(f1);
|
|
CREATE UNIQUE INDEX i46 ON t1(f1);
|
|
CREATE UNIQUE INDEX i45 ON t1(f1);
|
|
CREATE UNIQUE INDEX i44 ON t1(f1);
|
|
CREATE UNIQUE INDEX i43 ON t1(f1);
|
|
CREATE UNIQUE INDEX i42 ON t1(f1);
|
|
CREATE UNIQUE INDEX i41 ON t1(f1);
|
|
CREATE UNIQUE INDEX i40 ON t1(f1);
|
|
CREATE UNIQUE INDEX i39 ON t1(f1);
|
|
CREATE UNIQUE INDEX i38 ON t1(f1);
|
|
CREATE UNIQUE INDEX i37 ON t1(f1);
|
|
CREATE UNIQUE INDEX i36 ON t1(f1);
|
|
CREATE UNIQUE INDEX i35 ON t1(f1);
|
|
CREATE UNIQUE INDEX i34 ON t1(f1);
|
|
CREATE UNIQUE INDEX i33 ON t1(f1);
|
|
CREATE UNIQUE INDEX i32 ON t1(f1);
|
|
CREATE UNIQUE INDEX i31 ON t1(f1);
|
|
CREATE UNIQUE INDEX i30 ON t1(f1);
|
|
CREATE UNIQUE INDEX i29 ON t1(f1);
|
|
CREATE UNIQUE INDEX i28 ON t1(f1);
|
|
CREATE UNIQUE INDEX i27 ON t1(f1);
|
|
CREATE UNIQUE INDEX i26 ON t1(f1);
|
|
CREATE UNIQUE INDEX i25 ON t1(f1);
|
|
CREATE UNIQUE INDEX i24 ON t1(f1);
|
|
CREATE UNIQUE INDEX i23 ON t1(f1);
|
|
CREATE UNIQUE INDEX i22 ON t1(f1);
|
|
CREATE UNIQUE INDEX i21 ON t1(f1);
|
|
CREATE UNIQUE INDEX i20 ON t1(f1);
|
|
CREATE UNIQUE INDEX i19 ON t1(f1);
|
|
CREATE UNIQUE INDEX i18 ON t1(f1);
|
|
CREATE UNIQUE INDEX i17 ON t1(f1);
|
|
CREATE UNIQUE INDEX i16 ON t1(f1);
|
|
CREATE UNIQUE INDEX i15 ON t1(f1);
|
|
CREATE UNIQUE INDEX i14 ON t1(f1);
|
|
CREATE UNIQUE INDEX i13 ON t1(f1);
|
|
CREATE UNIQUE INDEX i12 ON t1(f1);
|
|
CREATE UNIQUE INDEX i11 ON t1(f1);
|
|
CREATE UNIQUE INDEX i10 ON t1(f1);
|
|
CREATE UNIQUE INDEX i9 ON t1(f1);
|
|
CREATE UNIQUE INDEX i8 ON t1(f1);
|
|
CREATE UNIQUE INDEX i7 ON t1(f1);
|
|
CREATE UNIQUE INDEX i6 ON t1(f1);
|
|
CREATE UNIQUE INDEX i5 ON t1(f1);
|
|
CREATE UNIQUE INDEX i4 ON t1(f1);
|
|
CREATE UNIQUE INDEX i3 ON t1(f1);
|
|
CREATE UNIQUE INDEX i2 ON t1(f1);
|
|
CREATE UNIQUE INDEX i1 ON t1(f1);
|
|
INSERT INTO t1 VALUES (REPEAT('a', 767));
|
|
connection node_2;
|
|
SELECT COUNT(*) = 1 FROM t1;
|
|
COUNT(*) = 1
|
|
1
|
|
SELECT LENGTH(f1) = 767 FROM t1;
|
|
LENGTH(f1) = 767
|
|
1
|
|
EXPLAIN SELECT COUNT(*) = 1 FROM t1 FORCE KEY (PRIMARY) WHERE f1 = REPEAT('a', 767);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const PRIMARY PRIMARY 769 const 1
|
|
SELECT COUNT(*) = 1 FROM t1 FORCE KEY (PRIMARY) WHERE f1 = REPEAT('a', 767);
|
|
COUNT(*) = 1
|
|
1
|
|
EXPLAIN SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i1) WHERE f1 = REPEAT('a', 767);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const i1 i1 769 const 1 Using index
|
|
SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i1) WHERE f1 = REPEAT('a', 767);
|
|
COUNT(*) = 1
|
|
1
|
|
EXPLAIN SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i63) WHERE f1 = REPEAT('a', 767);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const i63 i63 769 const 1 Using index
|
|
SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i63) WHERE f1 = REPEAT('a', 767);
|
|
COUNT(*) = 1
|
|
1
|
|
INSERT INTO t1 VALUES (REPEAT('b', 767));
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
connection node_1;
|
|
SELECT COUNT(*) = 2 FROM t1;
|
|
COUNT(*) = 2
|
|
1
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
DELETE FROM t1 WHERE f1 = REPEAT('b', 767);
|
|
connection node_1;
|
|
SET AUTOCOMMIT=OFF;
|
|
START TRANSACTION;
|
|
SELECT COUNT(*) = 1 FROM t1;
|
|
COUNT(*) = 1
|
|
1
|
|
INSERT INTO t1 (f1) VALUES (REPEAT('c', 767));
|
|
ROLLBACK;
|
|
SELECT COUNT(*) = 1 FROM t1;
|
|
COUNT(*) = 1
|
|
1
|
|
connection node_2;
|
|
START TRANSACTION;
|
|
SET AUTOCOMMIT=OFF;
|
|
SELECT COUNT(*) = 1 FROM t1;
|
|
COUNT(*) = 1
|
|
1
|
|
connection node_1;
|
|
START TRANSACTION;
|
|
connection node_2;
|
|
START TRANSACTION;
|
|
connection node_1;
|
|
UPDATE t1 SET f1 = REPEAT('e', 767);
|
|
connection node_2;
|
|
UPDATE t1 SET f1 = REPEAT('f', 767);
|
|
connection node_1;
|
|
COMMIT;
|
|
connection node_2;
|
|
COMMIT;
|
|
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
|
|
DROP TABLE t1;
|