I was wondering if maybe your SELECTs could be optimized? Are you using an index? MySQL can handle billions of rows w/o a problem.
-will Real-time Chat: irc.freenode.net -> #mysql ( http://www.mysql.com/doc/en/IRC.html ) ----- Original Message ----- From: "Julian Zottl" <[EMAIL PROTECTED]> To: "Andy Eastham" <[EMAIL PROTECTED]>; "Mysql List" <[EMAIL PROTECTED]> Sent: Friday, November 21, 2003 7:56 AM Subject: RE: DB design question - shell scripting... > Andy, > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
