1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
CREATE TABLE t1 (
g GEOMETRY NOT NULL
) ENGINE=InnoDB;
ALTER TABLE t1 ADD SPATIAL(g);
SET @g1 = ST_GeomFromText('POINT(10 10)');
SET @g2 = ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))');
SET @g3 = ST_GeomFromText('POLYGON((1.79769e+308 1.79769e+308, 20 5, -1.79769e+308 -1.79769e+308, 1.79769e+308 1.79769e+308))');
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRContains(g, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL g NULL NULL NULL 1 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1);
ST_AsText(g)
INSERT INTO t1 VALUES(@g1);
INSERT INTO t1 VALUES(@g2);
INSERT INTO t1 VALUES(@g3);
SELECT ST_AsText(g) FROM t1;
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
POLYGON((1.79769e308 1.79769e308,20 5,-1.79769e308 -1.79769e308,1.79769e308 1.79769e308))
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRContains(g, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 1 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1);
ST_AsText(g)
POINT(10 10)
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRDisjoint(g, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 2 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1);
ST_AsText(g)
POINT(10 10)
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBREquals(g, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 1 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1);
ST_AsText(g)
POINT(10 10)
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRIntersects(g, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 1 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1);
ST_AsText(g)
POINT(10 10)
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 1 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1);
ST_AsText(g)
POINT(10 10)
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRContains(g, @g2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 1 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRDisjoint(g, @g2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 1 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBREquals(g, @g2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 1 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRIntersects(g, @g2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 2 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range g g 34 NULL 2 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRContains(g, @g3);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g3);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
POLYGON((1.79769e308 1.79769e308,20 5,-1.79769e308 -1.79769e308,1.79769e308 1.79769e308))
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRDisjoint(g, @g3);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g3);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
POLYGON((1.79769e308 1.79769e308,20 5,-1.79769e308 -1.79769e308,1.79769e308 1.79769e308))
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBREquals(g, @g3);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g3);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
POLYGON((1.79769e308 1.79769e308,20 5,-1.79769e308 -1.79769e308,1.79769e308 1.79769e308))
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRIntersects(g, @g3);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g3);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
POLYGON((1.79769e308 1.79769e308,20 5,-1.79769e308 -1.79769e308,1.79769e308 1.79769e308))
EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g3);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where
SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g3);
ST_AsText(g)
POINT(10 10)
POLYGON((5 5,20 5,20 21,5 21,5 5))
POLYGON((1.79769e308 1.79769e308,20 5,-1.79769e308 -1.79769e308,1.79769e308 1.79769e308))
DROP TABLE t1;
|
