I am no DBA and that is becoming more apparent daily, as such I am having trouble keeping my query times reasonable. I have about 3.57 Million records ( at about 3.43 GB ) and it takes about 2 minutes to run simple queries. I have done the simple things that I know to do like indexing but most of the data I don't need to keep around and so I was thinking I could have a field called 'days2keep' in which certain entries that I need to keep for longer could be noted in the record itself. I would then have a cron job run daily or weekly to cull any records older than the 'days2keep' field. I have looked around and I have not seen any references to a similar idea. Does it make sense to do it this way or are there pitfalls that I am unaware of?
FWIW: I don't think that it matters but I am considering switching from MySQL to PostgreSQL.
After looking around it seems like the more common approach is to do horizontal partitioning of the data but I only need to keep some records for much longer so it seems like my original solution would be best. I am just a little wary of spending the time implementing it without some feedback from more experienced users.
Thanks to everyone for the insights below. The biggest take away here is that I still have a LOT to learn and understand so here are the steps I plan to take:
1) Analyze my queries to make sure I understand why they are taking so long.
2) Look into multi-column indexing to see if that might solve my problem.
3) Figure out the type of partitioning I might consider (horiz. not an option).
4) Figure out which solution keeps the database management and complexity to a minimum (esp. in regards to backup/restore and replication to dev space). I am using in Django so up until now it has been fairly easy to manage (for a non-DBA person).
5) Pick a solution and implement
6) Ask for a raise
7) drink beer