On Tue, May 22, 2007 23:29, PrzemysÅaw Klein said: > Martijn Tonies wrote: >> Hi, >> >> >>> I'm working on quite big database. It consists of about 200 tables. >>> Additionaly about 50 tables are per year (because of annual data). It >>> means every year new 50 tables will have to appear in application. And >>> now I have a question. Should I use separate databases for "annual" >>> data >>> (i.e. db2006, db2007, etc...) (i don't need constraints on that >>> (annual) >>> tables) or put all the tables in one database? Is there any way to >>> 'catalogue'/organize tables within one database (namespace/schema)? >>> >>> Any thoughts? >>> >> >> Yes, in my opinion, you should use the same tables for each year. So no >> "tables per year" or "databases per year", unless there is a very very >> specific >> reason for this. >> >> Having tables on a per-year basis also means you cannot do cross-year >> queries easily and you have to adjust your queries according to the >> current >> year. >> >> Martijn Tonies >> Database Workbench - development tool for MySQL, and more! >> Upscene Productions >> http://www.upscene.com >> My thoughts: >> http://blog.upscene.com/martijn/ >> Database development questions? Check the forum! >> http://www.databasedevelopmentforum.com >> >> >> > The reason of distribute annual data into different tables is that they > are NOT small. They store business documents in my company and can count > about 500k rows (and will grow each year). After performance tests we > did, it occurs that keeping those data in one table (with additional > column 'year') wouldn't meet our response time requirements. > I realize that this approach is not proper from relational point of > view, but it seems that we must separate annual data. Now, the question > is: if we should keep them in one database (and be prepared for database > with approx 500 tables after 3-4 years) or in multiple databases. > > Regards, > > -- > _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ > Przemek Klein ([EMAIL PROTECTED]) > Assuming you are using MYISAM table types, each table requires at least three files on the disk. If they are in one database they will all be in one directory (how fast is your OS at finding the files in its directory structure?). Are they going to be opened at the same time (how many file handles can you have open at once?)? If separate databases, how do you intend to connect to them (single connection specifying database or multiple connections).
My feeling is that you would not gain anything by having separate databases. Good luck. ------ William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
