Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following:
#!/bin/sh
date=`date "+%m%d%Y"`
export date
mysql -u root -p < createdb.sql
Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( ..... ) TYPE=MyISAM;
But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas?
Julian
At 12:37 PM 11/21/2003 +0000, Andy Eastham wrote:
Julian,
Your design is sound in my opinion. An area you probably need to consider is when you need to search across a day boundary.
You will need to make the application aware that it needs to search across a day boundary, so that it searches two tables with a union where necessary. It will also need to know what the oldest table is, so that it doesn't try to do a union with a table that doesn't exist.
Alternatively, you could always search across three tables - so that you always union with the one before and one after the required time window. Of course, you again need to check that you're not searching the earliest or latest available table, and if so, modify the union so that you don't try to search a non-existent table.
Hope this helps,
Andy
> -----Original Message----- > From: Julian Zottl [mailto:[EMAIL PROTECTED] > Sent: 21 November 2003 12:03 > To: [EMAIL PROTECTED] > Subject: DB design question > > > Hello all, > I am designing a database right now that will have between > 300-400k inserts > per day. I need to keep this information for approximately 3 months and > will probably do 5-10 reads on the data set per day. I've been > storing it > in one table up to now (only col.), but the searches are becoming > more and > more of a problem. I'd like to break it up so that I have one table for > every day, and then I'll just delete the trailing days when I > create a new > day. So I would have 90 tables of roughly 350k records instead > of a single > table with 6+ million records. What do you al think of this design? I'm > making an assumption that it will make my searches a lot faster for a > single day (I doubt I would ever need to search on more than one > day). Thanks! > Julian Zottl > Unix Systems Administrator > NASA HQ - 202-358-1682 > > > -- > 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]
Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
