CREATE TABLE t1(id INT PRIMARY KEY, x INT, y INT, KEY (x)) SECONDARY_ENGINE MOCK; INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 1), (3, 1, 1), (4, 1, 1), (5, 1, 1), (6, 1, 1), (7, 1, 1), (8, 2, 2), (9, 2, 2), (10, 3, 3); ALTER TABLE t1 SECONDARY_LOAD; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK # Uses the primary index to determine 10% filtering effect (one row). EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id = 1; EXPLAIN -> Filter: (t1.id = 1) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Uses the secondary index to determine 70% filtering effect (matches # rows 1 to 7). EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1; EXPLAIN -> Filter: (t1.x = 1) (rows=7) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Uses the secondary index to determine 20% filtering effect (matches # rows 8 and 9). EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2; EXPLAIN -> Filter: (t1.x = 2) (rows=2) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Uses the secondary index to determine 10% filtering effect (matches # row 10). EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3; EXPLAIN -> Filter: (t1.x = 3) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # No index on y, so a heuristic is used for the filtering effect. EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE y = 1; EXPLAIN -> Filter: (t1.y = 1) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Ignoring indexes cause heuristics to be used. EXPLAIN FORMAT=TREE SELECT * FROM t1 USE INDEX () WHERE id = 1; EXPLAIN -> Filter: (t1.id = 1) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 USE INDEX () WHERE x = 1; EXPLAIN -> Filter: (t1.x = 1) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 IGNORE INDEX (PRIMARY) WHERE id = 1; EXPLAIN -> Filter: (t1.id = 1) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 IGNORE INDEX (x) WHERE x = 1; EXPLAIN -> Filter: (t1.x = 1) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # No matching rows. Rounds up estimates to one matching row (10%). EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id = 100; EXPLAIN -> Filter: (t1.id = 100) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 100; EXPLAIN -> Filter: (t1.x = 100) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Matches two rows in the primary index (20%). EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id = 3 OR id = 7; EXPLAIN -> Filter: ((t1.id = 3) or (t1.id = 7)) (rows=2) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Matches three rows in the secondary index (30%). EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2 OR x = 3; EXPLAIN -> Filter: ((t1.x = 2) or (t1.x = 3)) (rows=3) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Matches all rows in the secondary index (100%). EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1 OR x = 2 OR x = 3; EXPLAIN -> Filter: ((t1.x = 1) or (t1.x = 2) or (t1.x = 3)) (rows=10) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Ranges in the primary index. EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 0; EXPLAIN -> Filter: (t1.id > 0) (rows=10) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 1; EXPLAIN -> Filter: (t1.id > 1) (rows=9) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 3; EXPLAIN -> Filter: (t1.id > 3) (rows=7) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 9; EXPLAIN -> Filter: (t1.id > 9) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id > 10; EXPLAIN -> Filter: (t1.id > 10) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id < 1; EXPLAIN -> Filter: (t1.id < 1) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id < 3; EXPLAIN -> Filter: (t1.id < 3) (rows=2) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id < 10; EXPLAIN -> Filter: (t1.id < 10) (rows=9) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE id BETWEEN 3 AND 7; EXPLAIN -> Filter: (t1.id between 3 and 7) (rows=5) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Ranges in the secondary index. EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 1; EXPLAIN -> Filter: (t1.x < 1) (rows=1) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 2; EXPLAIN -> Filter: (t1.x < 2) (rows=7) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 3; EXPLAIN -> Filter: (t1.x < 3) (rows=9) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x < 4; EXPLAIN -> Filter: (t1.x < 4) (rows=10) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x BETWEEN 1 AND 2; EXPLAIN -> Filter: (t1.x between 1 and 2) (rows=9) -> Table scan on t1 in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one # Indexes should only be used during optimization. Verify that the # execution plan for this query does not use DYNAMIC RANGE access. # (No occurrence of "Range checked for each record" in the plan.) EXPLAIN FORMAT=TREE SELECT * FROM t1 a, t1 b WHERE a.id < b.id; EXPLAIN -> Filter: (a.id < b.id) (rows=33.3) -> Inner hash join (no condition) (rows=33.3) -> Table scan on b in secondary engine MOCK (rows=10) -> Hash -> Table scan on a in secondary engine MOCK (rows=10) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one DROP TABLE t1; # Getting range statistics from spatial indexes is not supported, # since spatial indexes are not ordered. CREATE TABLE geom(g GEOMETRY NOT NULL SRID 0, SPATIAL INDEX(g)) SECONDARY_ENGINE MOCK; INSERT INTO geom VALUES (POINT(1, 1)), (POINT(2, 2)), (POINT(3, 3)); ALTER TABLE geom SECONDARY_LOAD; ANALYZE TABLE geom; Table Op Msg_type Msg_text test.geom analyze status OK EXPLAIN FORMAT=TREE SELECT ST_AsText(g) FROM geom WHERE MBRContains(POLYGON(LINESTRING(POINT(0,0), POINT(3,0), POINT(3,3), POINT(0,0))), g); EXPLAIN -> Filter: mbrcontains((polygon(linestring(point(0,0),point(3,0),point(3,3),point(0,0)))),geom.g) (rows=3) -> Table scan on geom in secondary engine MOCK (rows=3) Warnings: Note 1003 Query is executed in secondary engine; the actual query plan may diverge from the printed one DROP TABLE geom;