CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL, c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; CREATE SPATIAL INDEX idx1 on tab(c2 ASC); CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968'; CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=8 ; CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=4 COMMENT 'Spatial index on Geometry type column'; SHOW INDEXES FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored tab 0 PRIMARY 1 c1 A 0 NULL NULL BTREE NO tab 1 idx1 1 c2 A NULL 32 NULL SPATIAL NO tab 1 idx2 1 c3 A NULL 32 NULL SPATIAL wl6968 NO tab 1 idx3 1 c4 A NULL 32 NULL SPATIAL NO tab 1 idx4 1 c5 A NULL 32 NULL SPATIAL Spatial index on Geometry type column NO INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK # Test the MBRContains SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1+0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRWithin SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the ST_Crosses SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRDisjoint SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) 5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) 6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010)) 7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010)) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where # Test the MBREquals SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the Overelaps SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where # Test the ST_Touches SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRContains SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRDisjoint SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) 5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) 6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010)) 7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010)) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where # Test the MBREquals SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRintersects SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBROverelaps SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRTouches SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRTouches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where # Test with Procedure CREATE PROCEDURE proc_wl6968() BEGIN SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); END | CALL proc_wl6968(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the Delete & Update SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) DELETE FROM tab WHERE MBRContains(tab.c4, @g1); SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) DELETE FROM tab WHERE MBRContains(tab.c4, @g1); SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK SET @g1 = ST_GeomFromText('POLYGON((100 200,1010 1010,1020 1020,500 300,300 200,100 300,100 200))'); SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK SET @g1 = ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'); SET @g2 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); UPDATE tab SET C4 = @g2 WHERE ST_Crosses(tab.c4, @g1); SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g2) ORDER BY c1; c1 ST_Astext(c4) CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK DROP TABLE tab; DROP PROCEDURE proc_wl6968; CREATE TABLE tab(c1 int ,c2 POINT NOT NULL, c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; CREATE SPATIAL INDEX idx1 on tab(c2 ASC); CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968'; CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=2 ; CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=8 COMMENT 'Spatial index on Geometry type column'; SHOW INDEXES FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored tab 1 idx1 1 c2 A NULL 32 NULL SPATIAL NO tab 1 idx2 1 c3 A NULL 32 NULL SPATIAL wl6968 NO tab 1 idx3 1 c4 A NULL 32 NULL SPATIAL NO tab 1 idx4 1 c5 A NULL 32 NULL SPATIAL Spatial index on Geometry type column NO INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); INSERT INTO tab(c1,c2,c3,c4,c5) VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK # Test the MBRContains SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRWithin SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the ST_Crosses SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRDisjoint SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) 5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) 6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010)) 7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010)) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where # Test the MBREquals SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRintersects SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the Overelaps SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where # Test the ST_Touches SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRContains SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRDisjoint SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) 5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) 6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010)) 7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010)) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where # Test the MBREquals SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRintersects SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBROverelaps SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRTouches SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRTouches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where # Test with Procedure CREATE PROCEDURE proc_wl6968() BEGIN SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBREquals(tab.c4, @g1); EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); END | CALL proc_wl6968(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the Delete & Update SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); SET @g2 = ST_GeomFromText( 'POLYGON((1 1,2 2,3 3,10 3,5 1,1 1))'); UPDATE tab SET C4 = @g2 WHERE MBROverlaps(tab.c4, @g1); SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((1 1,2 2,3 3,10 3,5 1,1 1)) CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK DROP TABLE tab; DROP PROCEDURE proc_wl6968; CREATE TABLE tab(c1 int AUTO_INCREMENT PRIMARY KEY,c2 POINT NOT NULL, c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; CREATE SPATIAL INDEX idx1 on tab(c2 ASC); CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968'; CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=16 ; CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=16 COMMENT 'Spatial index on Geometry type column'; SHOW INDEXES FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored tab 0 PRIMARY 1 c1 A 0 NULL NULL BTREE NO tab 1 idx1 1 c2 A NULL 32 NULL SPATIAL NO tab 1 idx2 1 c3 A NULL 32 NULL SPATIAL wl6968 NO tab 1 idx3 1 c4 A NULL 32 NULL SPATIAL NO tab 1 idx4 1 c5 A NULL 32 NULL SPATIAL Spatial index on Geometry type column NO INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'), ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'), ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'), ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'), ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'), ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))')); INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'), ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'), ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')); INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'), ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'), ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))')); INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'), ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))')); INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'), ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))')); INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'), ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'), ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))')); INSERT INTO tab(c2,c3,c4,c5) VALUES(ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'), ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status Engine-independent statistics collected test.tab analyze status OK # Test the MBRContains SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRWithin SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the ST_Crosses SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRDisjoint SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) 5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) 6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010)) 7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010)) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where # Test the MBREquals SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRintersects SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the Overelaps SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where # Test the ST_Touches SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRContains SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) '); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRDisjoint SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300)) 5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010)) 6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010)) 7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010)) 9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010)) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where # Test the MBREquals SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRintersects SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBROverelaps SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where # Test the MBRTouches SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)') WHERE MBRTouches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where # Test the Delete & Update SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'); SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010)) DELETE FROM tab WHERE MBREquals(tab.c4, @g1); SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) 1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) 2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50)) 3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1); SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c4) CHECK TABLE tab; Table Op Msg_type Msg_text test.tab check status OK DROP TABLE tab; # Test check constraint on spatial column CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB; ERROR HY000: Illegal parameter data types point and int for operation '>' CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(CAST(c1 AS BINARY) > 0) ) ENGINE=InnoDB; CREATE SPATIAL INDEX idx1 ON tab(c1) ; SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( `c1` point NOT NULL, SPATIAL KEY `idx1` (`c1`), CONSTRAINT `tab_const` CHECK (cast(`c1` as char charset binary) > 0) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SHOW INDEX FROM tab; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored tab 1 idx1 1 c1 A NULL 32 NULL SPATIAL NO set @g1 = ST_GeomFromText('POINT(-1 -2)'); SELECT ST_AsText(c1) FROM tab; ST_AsText(c1) DROP table tab; create table `t1`(`a` geometry not null,`b` linestring not null, primary key (`b`(192),`a`(141)),spatial key (`b`)) engine=innodb; insert into `t1` values( point(1,1), linestring(point(1,1),point(1,1)) ); insert into `t1` values ( polygon( linestring(point(1,1),point(1,1)), linestring(point(1,1),point(11,1)) ), linestring(point(1,1),point(1,1)) ); ERROR 23000: Column 'a' cannot be null select 1 from t1 where st_intersects( geometrycollection(point(1,-1)),b ); 1 drop table t1; CREATE TABLE t1(c1 POINT NOT NULL); DROP TABLE mysql.innodb_table_stats; CALL mtr.add_suppression("InnoDB: Table `mysql`.`innodb_table_stats` not found."); CALL mtr.add_suppression("InnoDB: Fetch of persistent statistics requested for table `test`.`t1` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead."); CREATE SPATIAL INDEX idx2 ON t1(c1); DROP TABLE t1; CREATE TABLE mysql.innodb_table_stats ( database_name varchar(64) COLLATE utf8_bin NOT NULL, table_name varchar(199) COLLATE utf8_bin NOT NULL, last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, n_rows bigint(20) unsigned NOT NULL, clustered_index_size bigint(20) unsigned NOT NULL, sum_of_other_index_sizes bigint(20) unsigned NOT NULL, PRIMARY KEY (`database_name`,`table_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;