Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: avg_row_length & data_length

    hi,
    i m using innodb engine
    I want to know the average size data of row for particular tableBy Analyzing database Information_schema table name TABLES
    column AVG_ROW_LENGTH and DATA_LENGTH

    (TABLE_ROWS) (AVG_ROW_LENGTH) (DATA_LENGTH)
    6 2730 16384
    6 2730 16384
    1 16384 16384
    0 0 16384

    for rows 0 still data_length= 16384 how ?
    for different table with different size of data showing data_length= 16384 how ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how? i dunno

    can we see your CREATE TABLE statements?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    I believe it is because InnoDB always allocates at least one block of data for the table and the block size is 16 kilobytes. Therefore, the size of a particular table will be a multiple of 16 kilobytes.

  4. #4
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by r937
    how? i dunno

    can we see your CREATE TABLE statements?
    Please find create table statement

    CREATE TABLE SME_CND (
    PKCNDID bigint(20) NOT NULL auto_increment,
    GROUPNAME varchar(64) NOT NULL,
    CODE varchar(32) NOT NULL,
    DESCRIPTION varchar(128) NOT NULL,
    FKCNDID bigint(20) default NULL,
    FKSTATUSCNDID bigint(20) default NULL,
    CREATEDBY bigint(20) default NULL,
    CREATEDATE datetime NOT NULL,
    MODIFIEDBY bigint(20) default NULL,
    MODIFIEDDATE datetime NOT NULL,
    ADDFLAG1 char(1) default NULL,
    ADDFLAG2 char(1) default NULL,
    ISDELETED char(1) default NULL,
    PRIMARY KEY (PKCNDID),
    UNIQUE KEY CODE (CODE),
    KEY IDX_FKCNDID (FKCNDID),
    KEY IDX_FKSTATUSCNDID (FKSTATUSCNDID),
    KEY IDX_CREATEDATE (CREATEDATE),
    KEY IDX_ISDELETED (ISDELETED),
    CONSTRAINT FK_SME_CND_SME_CND1 FOREIGN KEY (FKCNDID) REFERENCES SME_CND (PKCNDID) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT FK_SME_CND_SME_CND2 FOREIGN KEY (FKSTATUSCNDID) REFERENCES SME_CND (PKCNDID) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB AUTO_INCREMENT=550215 DEFAULT CHARSET=latin1;

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, did not help me

    i think snorp's answer probably explains it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by snorp
    I believe it is because InnoDB always allocates at least one block of data for the table and the block size is 16 kilobytes. Therefore, the size of a particular table will be a multiple of 16 kilobytes.
    Thanks dude

    As I analize some table have data_length 16348 other has 81920

    on which basis this is the differance for different table
    Last edited by ankur02018; 09-10-08 at 10:33.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •