------------------------------------------------------------------------------------------ [ST_Intersects] mysql> SHOW VARIABLES LIKE 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 8.0.17 | +---------------+--------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 1 | SIMPLE | h27ka23_utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1162 | 136.86591 | 35.17593 | | 1252 | 136.86624 | 35.17181 | | 1072 | 136.86620 | 35.17126 | | 1143 | 136.86618 | 35.17055 | | 1225 | 136.86614 | 35.16993 | | 1280 | 136.86472 | 35.17391 | | 1283 | 136.86401 | 35.17154 | | 1284 | 136.86397 | 35.17083 | | 1038 | 136.86393 | 35.17555 | | 1309 | 136.86352 | 35.17032 | | 1308 | 136.86348 | 35.16964 | | 1281 | 136.86409 | 35.17282 | | 1163 | 136.86384 | 35.17432 | | 1312 | 136.86208 | 35.16955 | | 1282 | 136.86219 | 35.17164 | | 1261 | 136.86081 | 35.17253 | | 1310 | 136.86043 | 35.16981 | | 1164 | 136.86062 | 35.17164 | | 1260 | 136.85991 | 35.17435 | | 1189 | 136.85927 | 35.17138 | | 1188 | 136.85919 | 35.17259 | | 1258 | 136.86256 | 35.17471 | | 1259 | 136.86159 | 35.17436 | +---------+-----------+----------+ 23 rows in set (5.11 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | h27ka23_utf8 | NULL | ALL | NULL | NULL | NULL | NULL | 12834 | 100.00 | Using where | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1038 | 136.86393 | 35.17555 | | 1072 | 136.86620 | 35.17126 | | 1143 | 136.86618 | 35.17055 | | 1162 | 136.86591 | 35.17593 | | 1163 | 136.86384 | 35.17432 | | 1164 | 136.86062 | 35.17164 | | 1188 | 136.85919 | 35.17259 | | 1189 | 136.85927 | 35.17138 | | 1225 | 136.86614 | 35.16993 | | 1252 | 136.86624 | 35.17181 | | 1258 | 136.86256 | 35.17471 | | 1259 | 136.86159 | 35.17436 | | 1260 | 136.85991 | 35.17435 | | 1261 | 136.86081 | 35.17253 | | 1280 | 136.86472 | 35.17391 | | 1281 | 136.86409 | 35.17282 | | 1282 | 136.86219 | 35.17164 | | 1283 | 136.86401 | 35.17154 | | 1284 | 136.86397 | 35.17083 | | 1308 | 136.86348 | 35.16964 | | 1309 | 136.86352 | 35.17032 | | 1310 | 136.86043 | 35.16981 | | 1312 | 136.86208 | 35.16955 | +---------+-----------+----------+ 23 rows in set (3.84 sec) ------------------------------------------------------------------------------------------ [ST_Within] mysql> SHOW VARIABLES LIKE 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 8.0.17 | +---------------+--------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 1 | SIMPLE | h27ka23_utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1283 | 136.86401 | 35.17154 | | 1284 | 136.86397 | 35.17083 | | 1261 | 136.86081 | 35.17253 | +---------+-----------+----------+ 3 rows in set (0.00 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | h27ka23_utf8 | NULL | ALL | NULL | NULL | NULL | NULL | 12834 | 100.00 | Using where | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1261 | 136.86081 | 35.17253 | | 1283 | 136.86401 | 35.17154 | | 1284 | 136.86397 | 35.17083 | +---------+-----------+----------+ 3 rows in set (3.02 sec) ------------------------------------------------------------------------------------------ [ST_Overlaps] mysql> SHOW VARIABLES LIKE 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 8.0.17 | +---------------+--------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 1 | SIMPLE | h27ka23_utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1162 | 136.86591 | 35.17593 | | 1252 | 136.86624 | 35.17181 | | 1072 | 136.86620 | 35.17126 | | 1143 | 136.86618 | 35.17055 | | 1225 | 136.86614 | 35.16993 | | 1280 | 136.86472 | 35.17391 | | 1038 | 136.86393 | 35.17555 | | 1309 | 136.86352 | 35.17032 | | 1308 | 136.86348 | 35.16964 | | 1281 | 136.86409 | 35.17282 | | 1163 | 136.86384 | 35.17432 | | 1312 | 136.86208 | 35.16955 | | 1282 | 136.86219 | 35.17164 | | 1310 | 136.86043 | 35.16981 | | 1164 | 136.86062 | 35.17164 | | 1260 | 136.85991 | 35.17435 | | 1189 | 136.85927 | 35.17138 | | 1188 | 136.85919 | 35.17259 | | 1258 | 136.86256 | 35.17471 | | 1259 | 136.86159 | 35.17436 | +---------+-----------+----------+ 20 rows in set (4.70 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | h27ka23_utf8 | NULL | ALL | NULL | NULL | NULL | NULL | 12834 | 100.00 | Using where | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1038 | 136.86393 | 35.17555 | | 1072 | 136.86620 | 35.17126 | | 1143 | 136.86618 | 35.17055 | | 1162 | 136.86591 | 35.17593 | | 1163 | 136.86384 | 35.17432 | | 1164 | 136.86062 | 35.17164 | | 1188 | 136.85919 | 35.17259 | | 1189 | 136.85927 | 35.17138 | | 1225 | 136.86614 | 35.16993 | | 1252 | 136.86624 | 35.17181 | | 1258 | 136.86256 | 35.17471 | | 1259 | 136.86159 | 35.17436 | | 1260 | 136.85991 | 35.17435 | | 1280 | 136.86472 | 35.17391 | | 1281 | 136.86409 | 35.17282 | | 1282 | 136.86219 | 35.17164 | | 1308 | 136.86348 | 35.16964 | | 1309 | 136.86352 | 35.17032 | | 1310 | 136.86043 | 35.16981 | | 1312 | 136.86208 | 35.16955 | +---------+-----------+----------+ 20 rows in set (3.20 sec) ------------------------------------------------------------------------------------------