Roman, the row count in SHOW TABLE STATUS is only an estimate based on 8 dives into the index tree.
You had a typical symptom of a fragmented table: space usage much bigger than you would expect. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: "Roman Neuhauser" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, September 24, 2003 1:30 PM Subject: Re: innodb: storage requirements > # [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ů (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ů (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]
