I have a table that contains 1.5 million rows, 39 columns, sales data of about 2 years and that grows every day.
I had no problem until we transferred it to a new server. We probably have less memory now.
Queries are currently taking a lot of time. Someone suggested partitioning the big table at the root of most performance issues, but I have a few questions.
- Is it wise to partition the table I've described and is it
likely to improve its performance?
- If I partition it, will
I need to make changes to my current INSERT or SELECT statements or
will they continue to work in the same way?
Does the partition
take a lot of time to perform? I'm afraid that with the slow performance,
something would happen mid-way and I would lose the data.
Should I divide it into years or months? (we have the habit
look at the numbers in the month but sometimes we take weeks or
years). And should I also partition the columns? (We have some
columns that we rarely or never use, but we might want to use them