1

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);

So 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...

  • I'm not sure about this, but maybe the index is not used because MBRContains already uses the "Minimum bounding rectangle" of your line. And by the way, your query checks if a geometry is contained in the "Minimum Bounding Rectangle" and not the line itself. Or maybe the index is not used because you are creating a geometry (line between two points) and the resulting geometry doesn't have an index. – Gob Tron Sep 5 '18 at 14:40
1

TIMESTAMP, DATETIME, INT, FLOAT, DECIMAL, etc are effectively "numbers". Numbers can be ordered/searched/etc with ordinary BTREE indexes. There is no need for SPATIAL for such one-dimensional quantities.

For date-like quantities, I like to do:

WHERE ts >= '2018-02-23'
  AND ts  < '2018-02-23' + INTERVAL 3 WEEK

INDEX(ts)

This avoids dealing with leap years, kludges like '23:59:59', inclusive/exclusive (not the careful choice of inequality operators), etc.

0

You have two issues likely with this,

  • Floating point rounding errors
  • MBRContains is not likely what you want, if you want to return true INCLUSIVE of end-points. For that I would use MBRIntersects.

Shy of that, I would assume this would work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.