Search code examples
mysqlperformancedatetimequery-optimizationgrafana

Searching big MySQL table on timestamp is slow

I'm having the following MySQL table called 'log_data' - MyISAM

No keys, indexes, whatsoever.

VarId INT(11)
VarValue float
CurDate datetime(3)

The purpose of this table is logging data, the VarId is an integer is pointing to a different table that provides the 'name' of this actual variable. The particular log_data table thus has all processdata, to be separated by VarId and most often within a speficic date range (CurDate). As logging interval per variable is around 100ms this table is growing rather fast. Current dataset has 200M records.

Example query:

SELECT
  CurDate AS "time",
  VarValue AS 'Motor Power'
FROM log_data
WHERE
  CurDate BETWEEN FROM_UNIXTIME(1699511240) AND FROM_UNIXTIME(1699543207) AND VarId = 6

I'm having issues finding out what would be the best way to speed up these type of queries. As i'm displaying these variables within Grafana a single panel can have 10 of these queries (with different VarId). A single panel then takes about 20 seconds to load.

What would be the best way to optimise these type of queries?

I've tried working with an Primary key 'id', indexing on the CurDate field but this didn't help. It even increased the query time to over 1 minute. Also switching From InnoDB to MyISAM gave a performance win but still I'd like it to be faster.

Solution

  • Add an index over two columns:

    ALTER TABLE log_data ADD INDEX (VarId, CurDate);

    The order of columns is important, and is informed by the conditions in your query. See my answer to Does Order of Fields of Multi-Column Index in MySQL Matter?

    You may also get a benefit out of adding the VarValue column to the index, making it a covering index. Then the query can get its results by reading the index alone, so it doesn't have to touch the table rows at all.

    Use this index instead of the one I showed above.

    ALTER TABLE log_data ADD INDEX (VarId, CurDate, VarValue);

    Use EXPLAIN to confirm that the query is using the index. The optimizer might decide it's not worth using the index, for instance, if the specific values you are searching for occur on a large portion of the rows.

    I would not recommend using MyISAM. Performance is nice, of course, but it's more important to support concurrent updates to the table and to not lose data. See my answer to MyISAM versus InnoDB.