Description:
mysql> CREATE TABLE t1 (p POINT);
Query OK, 0 rows affected (0.65 sec)
mysql> INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
Query OK, 1 row affected (0.11 sec)
mysql> ALTER TABLE t1 SECONDARY_ENGINE = test;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> ALTER TABLE t1 SECONDARY_ENGINE = null;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
Query OK, 1 row affected (0.09 sec)
mysql> EXPLAIN
-> SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
There are two rows of data in the table, but only one row is explicit
mysql> ANALYZE TABLE t1;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| testdb.t1 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.16 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
How to repeat:
CREATE TABLE t1 (p POINT);
INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
ALTER TABLE t1 SECONDARY_ENGINE = test;
SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
ALTER TABLE t1 SECONDARY_ENGINE = null;
INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)'));
EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
ANALYZE TABLE t1;
EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');
Description: mysql> CREATE TABLE t1 (p POINT); Query OK, 0 rows affected (0.65 sec) mysql> INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)')); Query OK, 1 row affected (0.11 sec) mysql> ALTER TABLE t1 SECONDARY_ENGINE = test; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)'); +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> ALTER TABLE t1 SECONDARY_ENGINE = null; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)')); Query OK, 1 row affected (0.09 sec) mysql> EXPLAIN -> SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)'); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) There are two rows of data in the table, but only one row is explicit mysql> ANALYZE TABLE t1; +-----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+---------+----------+----------+ | testdb.t1 | analyze | status | OK | +-----------+---------+----------+----------+ 1 row in set (0.16 sec) mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)'); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) How to repeat: CREATE TABLE t1 (p POINT); INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)')); ALTER TABLE t1 SECONDARY_ENGINE = test; SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)'); ALTER TABLE t1 SECONDARY_ENGINE = null; INSERT INTO t1 VALUES (ST_POINTFROMTEXT('POINT(1 2)')); EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)'); ANALYZE TABLE t1; EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=ST_POINTFROMTEXT('POINT(1 2)');