This is more of a general question as to how best to manage a database.
I have a MYSQL database which regularly gets updated by users with daily events, user activity and selection of daily events. This has been running for a couple of years and I thought perhaps it is best to archive old events and activities.
It this a good idea? What is the best practice for archiving and how is it achieved without messing up the database? Do I need to be concerned that tables may be holding too much data? Is there a time when the unique IDs for table fields can become too high?
A lot of questions I know, but I would appreciate answers to any of these questions, or perhaps information on how site like YouTube, Facebook, etc tackle this problem.
Thanks :)
If you are afraid of, or experience, performance problems, you can consider running maintenance queries like ANALYZE, REINDEX and so on.
If you really want to archive your data, you can run a scheduled task/cron job to put all unused items (ie older than xxxx) inside 'archive' tables ; you can even use the ARCHIVE Mysql engine that lets you define read-only and compressed tables to optimize data space usage.
You also have the option to partition your tables in order to split them into multiple 'sub-tables', that could be located in different directories/drives.
Anyway, I couldn't recomment any solution more than the others, since you didn't tell what is your exact problem (if you have one!). Let me know if I can help more.
About the auto_increment fields, the IDs will become too high if they reach the max value allowed by your auto_increment column type. What did you put? INT? BIGINT? Those allow very high values