Search code examples
mysqlsqlinnodbmyisam

How to speed up query for datetime in Mysql

SELECT *
FROM LOGS
WHERE datetime > DATE_SUB(NOW(), INTERVAL 1 MONTH)

I have a big table LOGS (InnoDB). When I try to get last month's data, the query waits too long.

I created an index for column datetime but it seems not helping. How to speed up this query?

Solution

  • Since the database records are inserted in oldest to newest, you could create 2 calls. The first call requesting the ID of the oldest record:

    int oldestRecordID = SELECT TOP 1 MIN(id) 
FROM LOGS 
WHERE datetime > DATE_SUB(NOW(), INTERVAL 1 MONTH)

    Then with that ID just request all records where ID > oldestRecordID:

    SELECT *
FROM LOGS
WHERE ID > oldestRecordID

    It's multiple calls, but it could be faster however I am sure you could combine those 2 calls too.