mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-02-12 00:15:35 +01:00
Code Issues Projects Releases Packages Wiki Activity Actions
mariadb/mysql-test/suite/innodb_gis/r/create_spatial_index.result
Monty 727491b72a Added test cases for preceding test 
This includes all test changes from
"Changing all cost calculation to be given in milliseconds"
and forwards.

Some of the things that caused changes in the result files:

- As part of fixing tests, I added 'echo' to some comments to be able to
  easier find out where things where wrong.
- MATERIALIZED has now a higher cost compared to X than before. Because
  of this some MATERIALIZED types have changed to DEPENDEND SUBQUERY.
  - Some test cases that required MATERIALIZED to repeat a bug was
    changed by adding more rows to force MATERIALIZED to happen.
- 'Filtered' in SHOW EXPLAIN has in many case changed from 100.00 to
  something smaller. This is because now filtered also takes into
  account the smallest possible ref access and filters, even if they
  where not used. Another reason for 'Filtered' being smaller is that
  we now also take into account implicit filtering done for subqueries
  using FIRSTMATCH.
  (main.subselect_no_exists_to_in)
  This is caluculated in best_access_path() and stored in records_out.
- Table orders has changed because more accurate costs.
- 'index' and 'ALL' for small tables has changed to use 'range' or
   'ref' because of optimizer_scan_setup_cost.
- index can be changed to 'range' as 'range' optimizer assumes we don't
  have to read the blocks from disk that range optimizer has already read.
  This can be confusing in the case where there is no obvious where clause
  but instead there is a hidden 'key_column > NULL' added by the optimizer.
  (main.subselect_no_exists_to_in)
- Scan on primary clustered key does not report 'Using Index' anymore
  (It's a table scan, not an index scan).
- For derived tables, the number of rows is now 100 instead of 2,
  which can be seen in EXPLAIN.
- More tests have "Using index for group by" as the cost of this
  optimization is now more correct (lower).
- A primary key could be preferred for a normal key, even if it would
  access more rows, as it's faster to do 1 lokoup and 3 'index_next' on a
  clustered primary key than one lookup trough a secondary.
  (main.stat_tables_innodb)

Notes:

- There was a 4.7% more calls to best_extension_by_limited_search() in
  the main.greedy_optimizer test.  However examining the test results
  it looked that the plans where slightly better (eq_ref where more
  chained together) so I assume this is ok.
- I have verified a few test cases where there was notable/unexpected
  changes in the plan and in all cases the new optimizer plans where
  faster.  (main.greedy_optimizer and some others)
2023-02-03 00:00:35 +03:00

1348 lines
75 KiB
Text
Raw Permalink Blame History

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;
Reference in a new issue View git blame Copy permalink