Re: innodb: storage requirements

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2003-09-23 21:56:55 +0300:
> From: "Roman Neuhauser" <[EMAIL PROTECTED]>
> > A simple question regarding storage allocated / reported by MySQL/InnoDB.
> > If BIGINT takes 8 bytes, and a table has exactly four BIGINT columns,
> > why is the reported avg. row length 148 bytes?
>
> the table is probably very fragmented. The command
> 
> ALTER TABLE pmsystem2.editor_competence_product TYPE=InnoDB;
> 
> will defragment it.

    ok, that *did* help, but makes me wonder about the usefulness of the
    SHOW TABLE STATUS output:

mysql> SHOW TABLE STATUS LIKE 'editor_competence_product'\G
*************************** 1. row ***************************
           Name: editor_competence_product
           Type: InnoDB
     Row_format: Fixed
           Rows: 225198
                 ^^^^^^
 Avg_row_length: 100
    Data_length: 22626304
Max_data_length: NULL
   Index_length: 23199744
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
 Create_options: 
        Comment: kompetence technik&#367; (kde co mohou opravovat); InnoDB free: 6144 
kB; (zipid) REFER pmsystem2/zip(id) ON DELETE CASCADE; (brandid) REFER 
pmsystem2/brand(id) ON DELETE CASCADE; (editorid) REFER pmsystem2/editor(id) ON DELETE 
CASCADE
1 row in set (0.12 sec)

mysql> ALTER TABLE editor_competence_product TYPE=InnoDB;
Query OK, 187654 rows affected (47.34 sec)
Records: 187654  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS LIKE 'editor_competence_product'\G
*************************** 1. row ***************************
           Name: editor_competence_product
           Type: InnoDB
     Row_format: Fixed
           Rows: 187265
                 ^^^^^^
 Avg_row_length: 64
    Data_length: 12124160
Max_data_length: NULL
   Index_length: 20021248
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
 Create_options: 
        Comment: kompetence technik&#367; (kde co mohou opravovat); InnoDB free: 57344 
kB; (zipid) REFER pmsystem2/zip(id) ON DELETE CASCADE; (brandid) REFER 
pmsystem2/brand(id) ON DELETE CASCADE; (editorid) REFER pmsystem2/editor(id) ON DELETE 
CASCADE
1 row in set (0.09 sec)

Also, is there a way to *measure* the fragmentation of a table? If there
is, how should the info be interpreted?

-- 
If you cc me or remove the list(s) completely I'll most likely ignore
your message.    see http://www.eyrie.org./~eagle/faqs/questions.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]