Hi, I've got a problem with HEAP tables (undoubtedly the way that I'm using them).
I'm running MySQL v.3.23.51 under Debian Linux with a 2.4.17 kernel. I've got a perl script which creates a HELP table and controls the maximum size via the 'max_rows=' option to 'create table'. When the script is told that the maximum table size is 100 Mb it works out how many rows can fit into a table this big. The output from 'show table status' for this table is : Name: Traffic_001 Type: HEAP Row_format: Fixed Rows: 115563 Avg_row_length: 54 Data_length: 6608680 Max_data_length: 8311626 Index_length: 988608 Data_free: 0 Create_options: max_rows=998643 The first problem is 'max_data_length'. The total space required for a single row I've set to 105 bytes (54 + index + overhead), hence the 'max_rows' value shown. However, 'Max_data_length' is shown as 8311626 which is equivalent to 79158 rows. Could someone explain why this is? Having read the documentation about HEAP tables quite a few times, I'm aware that I don't know enough about how MySQL allocates spare for them. If I say that I want the table to have a million rows, which I have more or less, is 'Max_data_length' set at creation time based on whatever memory is available, and from that point on is unchangable? If so, this suggests that for HEAP tables, I need to create it, saying how big I'd like it to be, and then use 'show table status' to find out how big it *actually* is. Is this true? What effect on the allowable maximum size for HEAP tables does the variable 'tmp_table_size' have? If anyone can help clear some of the fog, I'd appreciate it !! Regards, Paul Wilson Chime Communications --------------------------------------------------------------------- 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
