CODEWITHSUNDEEP
sqlsql-serverdatetimequery-optimizationwhere-clause
GreenSaber
GreenSaber

Reputation: 1148

Indexing an SQL table by datetime that is scaling

I have a large table that gets anywhere from 1-3 new entries per minute. I need to be able to find records at specific times which I can do by using a SELECT statement but it's incredibly slow. Lets say the table looks like this:

Device |     Date-Time     | Data |
-----------------------------------
1      | 2020-01-01 08:00  | 325
2      | 2020-01-01 08:01  | 384
1      | 2020-01-01 08:01  | 175
3      | 2020-01-01 08:01  | 8435
7      | 2020-01-01 08:02  | 784
.
.
.

I'm trying to get data like this:

SELECT *
FROM table
WHERE Date-Time = '2020-01-01 08:00' AND Device = '1'

I also need to get data like this:

SELECT *
FROM table
WHERE Date-Time > '2020-01-01 08:00' Date-Time < '2020-01-10 08:00' AND Device = '1'

But I don't know what the Date-Time will be until requested. In this case, I will have to search the entire table for these times. Can I index the start of the day so I know where dates are?

Is there a way to index this table in order to dramatically decrease the queries? Or is there a better way to achieve this?

I have tried indexing the Date-Time column but I did not decrease the query time at all.

Upvotes: 0

Views: 293

Answers (2)

CleanBold
CleanBold

Reputation: 1592

You can use TimeInMilliseconds as new column and populate it with milliseconds from the year 1970 and create Index on this column. TimeInMilliseconds will always be unique number and it will help the index to search queries faster.

Upvotes: 0

GMB
GMB

Reputation: 222432

For this query:

SELECT *
FROM mytable
WHERE date_time = '2020-01-01 08:00' AND device = 1

You want an index on mytable(date_time, device). This matches the columns that come into play in the WHERE clause, so the database should be able to lookup the matching rows efficiently.

Note that I removed the single quotes around the literal value given to device: if this is an integer, as it looks like, then it should be treated as such.

The ordering of the column in the index matters; generally, you want the most restrictive column first - from the description of your question, this would probably be date_time, hence the above suggestion. You might want to try the other way around as well (so: mytable(device, date_time)).

Another thing to keep in mind from performance perspective: you should probably enumerate the columns you want in the SELECT clause; if you just want a few additional columns, then it can be useful to add them to the index as well; this gives you a covering index, that the database can use to execute the whole query without even looking back at the data.

Say:

SELECT date_time, device, col1, col2
FROM mytable
WHERE date_time = '2020-01-01 08:00' AND device = 1

Then consider:

mytable(date_time, device, col1, col2)

Or:

mytable(device, date_time, col1, col2)

Upvotes: 2

Related Questions