I've had no problems partitioning data in this exact same manner. However my timestamp column is always pre-computed in the application code because it is neccesary to round it to the last 5 minute interval so I would not encounter the issue you mention. I'd recommend simply computing the timestamp in advance. If you have a database substantial enough you need this partitioning, and such that you can't delete as fast as you insert presently, then I'm guessing the database server is your major bottleneck in overall performance anyway. In which case offloading the date computation to the application server is probably a smart move regardless.
The only complication we have had since moving to this partioning scheme is that its more complicated to create reports across tables (we have to deal with both user local time and GMT, so sometimes a daily report will span two tables). John A. McCaskey -----Original Message----- From: Andrew Hill [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 21, 2004 8:00 AM To: [EMAIL PROTECTED] Subject: Splitting data across tables Hi all, We have quite a large table, and are considering splitting the data into daily tables - something like tblname_20040721, tblname_20040722, etc. Hopefully, this will allow us to more easily delete old data through dropping the old tables, instead of using DELETE. (At present, we're inserting data into this table faster than we can remove it...) I was wondering if anyone has any experience and advice to give on going down this path, that they would be willing to share? Specifically, I'm concerned by the possibility of inserting data into the "wrong" table: Say a client notes that the date is 20040721 23:59:58, and so decides to insert the data into the tblname_20040721 table - and then, when the insert happens, it's now 20040722 00:00:01. If the table has a timestamp column that is set by the MySQL server, then the timestamp will be in 20040722, but the data will be inserted into the table for 20040721. Obviously, this could also happen should NTP get out of whack... Perhaps it would be better to insert the timestamp, rather than letting MySQL set it. Or, is there perhaps a way to let MySQL select which table to perform the insert into, based on the time? Any ideas or comments? TIA, -- Andrew Hill Developer Awarez Ltd. Kirkman House, 12-14 Whitfield Street, London W1T 2RF T: +44 (0)20 7299 7370 F: +44 (0)20 7299 7374 ---------------------------------------------------------------------- The information in this email is confidential and is intended solely for the addressee. It may be legally privileged. The contents are not to be disclosed to anyone other than the addressee and access to this email by anyone else is unauthorised. Unauthorised recipients are requested to preserve the confidentiality of this email and to advise the sender immediately of any error in transmission. Any disclosure, copying, distribution or action taken or omitted to be taken in reliance upon the contents of this email by unauthorised recipients is prohibited and may be unlawful. ---------------------------------------------------------------------- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
