Hi,
You could do the partitioning logic on the application side. For example you can create a new table every month with a different suffix corresponding to the year/month number. Then you add the info about this new table into a master description table, which holds one row per created table, together with creation time and last modification. Your "single fact table" will actually be a bunch of physical tables whose size you then control quite accurately (if you kow the average data quantity inserted every month). When you want to query some data, you can first select which tables are concerned on the basis of the time interval you want to select data from (it's a very simple SELECT over the master description table). And then query each of these tables in turn (or maybe in parallel if you have tweaked your database so as to spread tables on different physical disks ;-)). If you often do statistical reports which cover only a period in time, this means you can avoid a lot of table scanning just by discarding tables which are outside of the time period you are considering ;-) An alternative is to use MERGE tables which are supposed to handle this stuff automatically, but I don't know if early table selection based on index values is done (if not, it would be slower than the manually-controlled method I described, because it would open every table to only find out that a pair of them would have been enough). I use it at work with multiple tables, multiple databases, two different archiving policies. It works fine and I begin to have a bit of data (10 gigabytes, albeit in several different "logical" tables). A neat thing is that you can even compress older tables, because they are read-only once you don't insert data anymore (see myisampack in the documentation). This saves some space (the amount of space saved depends on course on the nature of your data, but for a regular logging/statistical application I think you can expect at least a 30% gain). By the way : if your indexes take lots of place and some of them are slowly varying integer indexes - like timestamps - I recommend you specify PACK_KEYS=1 on table creation, this saves space too. (note : I'm talking about MyISAM here, of course) Good luck, Regards Antoine. >Hi, > >Does MySQL offering a similar functionality as Oracle does with table >partitioning? > >Oracle can split a table into several pieces where the pieces are - >usually - identified by a range of valus. >For example, one may split a table holding data on a daily basis into >monthly partitions. Oracle will store the data in the partition identified >by month of the new inserted data. >As the tablespace used by each partition can be specified this is very >usefull to spread the disk IO to several harddisks. > >The main reason for partitioning a table is to make VERY large tables more >performant. Oracle fetches only the partions needed by the SQL statement, >this means a full table scan will only process partitions identified by >the WHERE clause and not the full range of data. > >If MySQL does not offer this or a simmilar functionality, how does MySQL >perform on very large tables (I'm talking here about 10 Gigs+ of data >stored in a single fact table)? > >Best Regards, >Wolfgang --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
