I'm using MySQL 8.0. I have some table containing TIMESTAMP column and I need to select within TIMESTAMP range:
SELECT * FROM t_tbl WHERE ts BETWEEN @someDate1 AND @someDate2
I want to make this query as fast as possible so I decide to try GEOMETRY trick. First of all I add some magik geometry column:
ALTER TABLE t_tbl ADD COLUMN geom_ts GEOMETRY NOT NULL GENERATED ALWAYS AS (Point(TO_SECONDS(ts),0)) STORED; ALTER TABLE t_tbl ADD SPATIAL INDEX IDX_g_i (geom_ts);
ts momentum is really a point onto X (time) axis. Now to check ts is in some range I'm using such geometry expression:
SELECT * FROM t_tbl WHERE MBRCONTAINS(LineString(point(@someDate1,0),point(@someDate2,0)), geom_ts)
This works but EXPLAIN doesn't show me any spatial index usage - it performs tablescan...