mariadbで１分以上前の最初のデータを指定する（DATETIME型）

Question

環境：CentOS7、mariadb10.2

以下のフォーマットのテーブルがあります。

■TEST_TABLE
NUMBER int(8) AutoIncrement
DATETIME datetime
VALUE int(10)

テーブルには4から6秒置き（処理時間により多少前後する）にリアルタイムでデータを挿入しています。テーブルには最大３０日分/５０万件近くのデータが入っています。処理の中で「もっとも１分前に近いVALUEを取り出す」ことをしたいのですが、うまく速度が出ません。

□試したSQL
select VALUE from TEST_VALUE where DATETIME<'%s' order by NUMBER desc LIMIT 1
※%sには現在１分前の日付時刻

上記SQLではCerelonの開発機で試したところ、１回のSelectに0.1秒ほどかかってしまっています。
DATETIMEにindexを貼ってみましたがあまり効果がありませんでした。

回避策として「LIMIT句で、現在からさかのぼって15件拾ってから、その15件でループをまわして１分前のデータを取得する」ようにしたところ、0.01秒まで短縮できました。が、処理として美しくなく、１時間前となるとそのまま使えず、まだ早くなる余地があると思っています。

どなたか、アドバイスをお願いします。

Answer 1

現在時刻より60秒前、かつ現在時刻より70秒前の間のものだけをWHEREで絞り込んでみてはどうでしょうか。

SELECT
  VALUE
FROM
  TEST_VALUE
WHERE
  DATETIME BETWEEN
    CURRENT_TIMESTAMP - INTERVAL 70 SECOND
    AND CURRENT_TIMESTAMP - INTERVAL 60 SECOND
ORDER BY
  DATETIME ASC
LIMIT 1

Comments

[masaya_ohashi]

70秒、の部分はそのシステムで「最大どのくらいのズレが起こりうるのか」によって値を変えてください。

[tamo_solo]

了解です！のちほど試して見ます、しばしお待ちを...（６０－７０秒で取れなかった場合も、ロジックで考慮します）

[tamo_solo]

試しました。indexとの併用で満足のいく速度が出ました、ありがとうございます