Hello, I have a very large table with only 3 columns:
time_stamp(datetime), variable_name(varchar(40)), value(real)
which is used for storing measured values.
I've multiple queries which look like
Select avg(value) from my_table where variable_name = 'xxxx' and time_stamp between some_date and other_date
Select max(value)-min(value) from my_table where variable_name = 'xxxx' and time_stamp between some_date and other_date
To summarise, all of my queries are doing some math functions with the VALUE for a variable_name within a time period.
What would be the appropriate index for this table?
Currently i have a non clusetered index
create index on (variable_name, time_stamp) include value
Currently some of the queries are taking up to 30min. Is there a better way of indexing the table?
I am also going to create views or result tables with data for a period of time like 1 month. But this is another topic. For now I just want to clarify if my index is suitable.