How to keep database speed after a lot of data

Question

I have a table where Estate Agents can post jobs online.

Generally we expect 2000 entries per month, after a year that 24'000 entries!

Would this considerably slow the database down?

Should I export the data to a file if its older that a year?

If so how would I maintain data integrity

Answer 1

No, 24000 entries in a table is nowadays not considered a large amount of data at all. Of course I am make the assumption that your database is something like SQL Server, MySQL etc.

You would need to ensure that the table had indexes assigned to the relevant fields, e.g. the identity column or any column used regularly in WHERE clauses, as this would ensure that data could still be accessed quickly.

You should also definitely consider a back up schedule regardless of database size.

Answer 2

There's an old no-longer-on-topic question that is probably littered with useful information for you, and will at least serve as a starting point for researching and learning about database performance.

To be blunt... In the realm of "large volumes of data" 2,000 records per month is statistically indistinguishable from 0 records per month. You're not talking about a lot of data. So, unless there are some serious design problems with your database(s) and/or your application(s), you should be fine.

But the fact that you asked the question indicates that you don't know if you have any of these problems. So hopefully this will give you a good place to start. There's no magic bullet. There's no single thing you can do that will always make your database perform better. It depends a lot on how you use the data (how it's organized, whether it's more read-heavy or write-heavy, etc.) as to how you'd optimize it.

(Indeed, you'll want to explicitly avoid the mentality of "magic bullets" in this case. For example, I once worked with someone who was convinced that "adding indexes is how you improve database performance" because he was taught that at some impressionable point in his career. It's... not unilaterally true. And over-indexing or poorly-indexing can lead to dramatic performance problems in a database.)

Answer 3

Does the table have any indexes or a primary key? 24000 records is not much data but it doesn't cost anything to use indexes (correctly) to increase performance.

What is the table structure and which columns are you most likely to query the table on?