130 lines
4.7 KiB
Text
130 lines
4.7 KiB
Text
CREATE TABLE t1 (a INT, b INT, c INT, d INT,
|
|
KEY i_a(a), KEY i_b(b),
|
|
KEY i_ab(a,b), KEY i_c(c), KEY i_d(d));
|
|
INSERT INTO t1 VALUES
|
|
(1,1,1,1),(2,2,2,1),(3,3,3,1),(4,4,4,1),
|
|
(5,5,5,1),(6,6,6,1),(7,7,7,1),(8,8,8,1);
|
|
INSERT INTO t1 SELECT a,b, c + 10, d FROM t1;
|
|
INSERT INTO t1 SELECT a,b, c + 20, d FROM t1;
|
|
INSERT INTO t1 SELECT a,b, c + 40, d FROM t1;
|
|
INSERT INTO t1 SELECT a,b, c + 80, d FROM t1;
|
|
INSERT INTO t1 SELECT a,b, c + 160, d FROM t1;
|
|
ANALYZE TABLE t1;
|
|
|
|
|
|
# Check behavior of duplicated/intersected hints.
|
|
# First specified hint is applied and next conflicting/intersected hints
|
|
# are ignored with warning.
|
|
|
|
# JOIN_INDEX(t1) is ignored as intersected.
|
|
EXPLAIN SELECT /*+ INDEX(t1) JOIN_INDEX(t1) */ a FROM t1;
|
|
|
|
# INDEX(t1 i_d) is ignored as duplicated.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a, i_b, i_c) INDEX(t1 i_d) */ a FROM t1;
|
|
|
|
# JOIN_INDEX(t1 i_a, i_b) is ignored as intersected.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a) JOIN_INDEX(t1 i_a, i_b) JOIN_INDEX(t1 i_b) */ a FROM t1;
|
|
|
|
# GROUP_INDEX(t1 i_a, i_b) is ignored as intersected.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a) GROUP_INDEX(t1 i_a, i_b) GROUP_INDEX(t1 i_b) */ a FROM t1;
|
|
|
|
# GROUP_INDEX(t1) is ignored as intersected.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a) GROUP_INDEX(t1) */ a FROM t1;
|
|
|
|
# ORDER_INDEX(t1 i_a, i_b) is ignored as intersected.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a) ORDER_INDEX(t1 i_a, i_b) ORDER_INDEX(t1 i_b) */ a FROM t1;
|
|
|
|
# ORDER_INDEX(t1) is ignored as intersected.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a) ORDER_INDEX(t1) */ a FROM t1;
|
|
|
|
# ORDER_INDEX(t1 i_b) is ignored as intersected.
|
|
EXPLAIN SELECT /*+ ORDER_INDEX(t1 i_a, i_b) ORDER_INDEX(t1 i_b) INDEX(t1 i_c)*/ a FROM t1;
|
|
|
|
# INDEX(t1) is ignored as intersected.
|
|
EXPLAIN SELECT /*+ ORDER_INDEX(t1 i_a, i_b) GROUP_INDEX(t1 i_b) INDEX(t1)*/ a FROM t1;
|
|
|
|
# INDEX(t1) is ignored as intersected.
|
|
EXPLAIN SELECT /*+ ORDER_INDEX(t1) GROUP_INDEX(t1) INDEX(t1)*/ a FROM t1;
|
|
|
|
# Check the use of index hints.
|
|
|
|
# Force the use of i_a, i_b indexes, intersect(i_a,i_b) is used.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a, i_b) */ a FROM t1 WHERE a = 1 AND b = 2 AND c = 3;
|
|
|
|
# Force the use of i_a, i_ab indexes, i_ab index is used.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a, i_ab) */ a FROM t1 WHERE a = 1 AND b = 2 AND c = 3;
|
|
|
|
#Force the use of i_a, i_b, i_c indexes, i_c index is used.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a, i_b) JOIN_INDEX(t1 i_c) */ * FROM t1 WHERE a = 1 AND b = 2 AND c = 3;
|
|
|
|
# Ignore i_ab index, i_b index is used.
|
|
EXPLAIN SELECT /*+ NO_INDEX(t1 i_ab) */ a, max(b) FROM t1 WHERE b = 2 GROUP BY a;
|
|
|
|
# Ignore i_ab index, i_b index is used.
|
|
EXPLAIN SELECT /*+ NO_JOIN_INDEX(t1 i_ab) */ a, max(b) FROM t1 WHERE b = 2 GROUP BY a;
|
|
|
|
# Force i_ab index for GROUP BY, i_ab index scan is used.
|
|
EXPLAIN SELECT /*+ GROUP_INDEX(t1 i_ab) */ a, max(b) FROM t1 GROUP BY a;
|
|
|
|
# Force i_ab index for JOIN, i_ab loose index scan is used.
|
|
EXPLAIN SELECT /*+ JOIN_INDEX(t1 i_ab) */ a, max(b) FROM t1 GROUP BY a;
|
|
|
|
# Ignore i_ab for for sorting rows. i_a index is used for sorting rows.
|
|
EXPLAIN SELECT /*+ NO_ORDER_INDEX(t1 i_ab) */ a FROM t1
|
|
ORDER BY a;
|
|
|
|
# Ignore i_a for for sorting rows. Filesort is used for sorting rows.
|
|
EXPLAIN SELECT /*+ NO_ORDER_INDEX(t1 i_a) */ a FROM t1
|
|
ORDER BY a;
|
|
|
|
# Force i_ab index for sorting rows.
|
|
EXPLAIN SELECT /*+ ORDER_INDEX(t1 i_ab) */ a FROM t1
|
|
ORDER BY a;
|
|
|
|
# Force i_a index for sorting rows.
|
|
EXPLAIN SELECT /*+ ORDER_INDEX(t1 i_a) */ a FROM t1
|
|
ORDER BY a;
|
|
|
|
# Force i_a index for sorting rows.
|
|
EXPLAIN SELECT /*+ ORDER_INDEX(t1 i_a) */ * FROM t1
|
|
ORDER BY a;
|
|
|
|
# Ignore all indexes.
|
|
EXPLAIN SELECT /*+ NO_INDEX(t1) */ * FROM t1 WHERE a = 1 AND b = 2 AND c = 3;
|
|
|
|
# Check if old hints work if no new hint is specified.
|
|
EXPLAIN SELECT /*+ INDEX_MERGE(t1) */ * FROM t1 IGNORE INDEX (i_a)
|
|
WHERE a = 1 AND b = 2 AND c = 3;
|
|
|
|
# Check index hints with UPDATE/DELETE commands.
|
|
|
|
# Force i_a index for UPDATE.
|
|
EXPLAIN UPDATE /*+ INDEX(t1 i_a) */ t1 SET d = 1 WHERE a = 1 AND b = 2 AND c = 3;
|
|
|
|
# Force i_a index for DELETE.
|
|
EXPLAIN DELETE /*+ INDEX(t1 i_a) */ FROM t1 WHERE a = 1 AND b = 2 AND c = 3;
|
|
|
|
# Check index hints in views.
|
|
|
|
CREATE VIEW v1 AS SELECT /*+ NO_INDEX(t1 i_a,i_b) */ a FROM t1 WHERE
|
|
b IN (SELECT /*+ NO_INDEX(t1 i_ab,i_b) */ a FROM t1 WHERE a > 3)
|
|
ORDER BY a;
|
|
SHOW CREATE VIEW v1;
|
|
EXPLAIN SELECT a FROM v1;
|
|
EXPLAIN SELECT /*+ INDEX(ta i_a) */ ta.a FROM v1, t1 ta WHERE ta.a > 3;
|
|
|
|
CREATE VIEW v2 AS SELECT /*+ INDEX(ta i_a) */ ta.a FROM v1, t1 ta WHERE ta.a > 3;
|
|
SHOW CREATE VIEW v2;
|
|
EXPLAIN SELECT a FROM v2;
|
|
EXPLAIN SELECT /*+ INDEX(tb i_a) */ tb.a FROM v2, t1 tb WHERE tb.a > 3;
|
|
|
|
# Check that old hint is silently ignored if new hint is specified.
|
|
EXPLAIN SELECT /*+ INDEX(t1 i_a) */ * FROM t1 IGNORE INDEX(i_a)
|
|
WHERE a = 1 AND b = 2 AND c = 3;
|
|
|
|
# Check unsupported hints in view.
|
|
CREATE VIEW v3 AS SELECT /*+ INDEX_MERGE(t1) */a FROM t1
|
|
WHERE a = 1 AND b = 2 AND c = 3;
|
|
|
|
DROP VIEW v1, v2, v3;
|
|
DROP TABLE t1;
|