0

I just need advice on how I am going to optimize my database.

| Date | Time | Area | Block | Data1 | Data2 | Data3 | DataN |

I have that format of tables on my database. Each tables has 30-days of records with hundred thousands of records each data. Uploading of data to database is every morning. The data of yesterday will be uploaded.

The most common queries to run is grouped by Date or Date and Area or Date and Area and Block. Now, in order to make queries faster, I found out using indexes. I used indexes before. But that was when I am using a primary key. In this table, I didn't use primary key because it is totally unnecessary.

Now, I am really confused on how I am going to optimize this. Based on this reference on Column Considerations part, clustered index is not a good choice on Columns that undergo frequent changes. I am really confused.

Please help me if I need to use clustered index here or just non clustered or both.

ypercubeTM
99.7k13 gold badges217 silver badges306 bronze badges
asked Apr 5, 2014 at 4:22
3
  • I just provided an answer to this on your stack overflow version of this question. If you prefer, I can drop the answer in over here. Commented Apr 5, 2014 at 4:40
  • Clustered indexes are useful for columns / keys which are always inserted in increasing order ( serial, timestamp, .. ) and never updated. Commented Apr 5, 2014 at 17:05
  • You may want to consider sliding window partitioning Commented Apr 6, 2014 at 5:07

1 Answer 1

0

1/ If after upload you do not update the data, only delete the rows older than 30 days.

2/ And if you can sort your file for the upload by Date, Area, Block

=> Then a clustered index on Date,Area,Block will greatly benefit your aggregations on Date, Date + Area, Date+ Area+Block .

It will also help any request with a where condition on Date, Date + Area, Date + Area + Block , whatever the selected columns for the request.

answered Apr 5, 2014 at 19:09
2
  • I delete data everyday (16th day data), and upload everyday (yesterday data)... Commented Apr 9, 2014 at 7:50
  • As long as the insertion is ordered, there is no problem. Commented Apr 9, 2014 at 7:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.