Respones inline: On Tue, 30 Nov 2004 15:04:38 -0600, Nick Baker <[EMAIL PROTECTED]> wrote: > I am new to MySql and having difficulty understanding several issues. > > 1. Size. Is a practical size one should limit the DB to. It is my > understanding that there is no technical limit to the number of records or > the actual physical size (bytes) of a MySql DB.
I believe that the limit is based on the filesystem's max file size. That can be beat with the InnoDB table handler, as it lets you strip across multiple files, or even across raw partitions. > Surely there must be some point that efficiency, response time, etc. come > into play? As long as you're using indexes appropriately, you're slowdowns will be a logarithmic curve. The biggest table I've got right now is about 2 million rows. Not speedy, but not overly slow either. > Any wisdom someone would like to share on this? > > 2. Calculating or projecting future size. > > There are 9 tables in my DB. Seven tables contain static info. Using > MyPHPAdmin, I have been monitoring the two dynamic tables to get some idea > how much space is required. The results are shown below. I see a very > strange pattern. One note: Each day the oldest 95% of Table 1 is deleted, > using <CFQUERY "DELETE" function. I am wondering it the "DELETE" function > may be leaving a lot of trash in the DB? MySQL doesn't unallocate deleted rows with the DELETE command (unless you don't specify a WHERE clause). Rather, it keeps them around for reuse. They'll be freed eventually. There are ways to force them to be freed if you want, but there usually isn't much reason to do that. > Table 1 (Typical records is about 150 charcters) > > Records Size Overhead > 1,807 1.6 MB 1,280 KB > 2,983 1.6 MB 1,147 KB > 10,165 1.6 MB 92,532 Bytes > > Table 2 > > Records Size Overhead > 779 56.9 KB - > 787 57.4 KB - > 794 65.1 KB - > > Any help would be appreciated. > > Thanks, > > Nick > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/blog/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185750 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
