0

I'm creating a SQL database which holds roughly 30,000 records per year. after that year the previous records aren't that important just kept for statistical purposes. Here's my problem.Should I create a table every year so that the data is much separated Or keep everything in one table with a year column?

My reason to ask this question is I that even though i could hold a life time amount of records in a table would the performance be affected? like if i'm doing a query with only the records from this year i feel like having a table with only that year is faster than putting a WHERE year=**** on a table with records from every year. I have no idea if SQL keeps them a little separated when storing data with the same column value so that it only has to find that whole section when searched for a specific year.

I'm honestly really new at this it's just a project i'm doing for my school and explanations would be much appreciated.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Ranidu Lakshan
  • 11
  • 1
  • 2
    *Should I create a table every year* NO. *keep everything in one table with a year column* Yes. – Akina Jun 09 '23 at 05:13
  • Please provide enough code so others can better understand or reproduce the problem. – Community Jun 09 '23 at 06:22
  • 30K rows is "tiny"; don't worry about performance unless you have no indexes. Don't bother with a `year` column. (There may be rare exceptions; let's see your queries.) – Rick James Jun 10 '23 at 01:27
  • @RickJames thank you. i do need to know what year to query since otherwise the yearly statistics would be filled with every record from every year. Thank you for telling me about indexing i didn't know about it and it was really helpful. much love <3 – Ranidu Lakshan Jun 10 '23 at 07:14

0 Answers0