Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33

    Unanswered: please explain :)

    Can someone please explain the follwing to me :

    when doing a : show table status;

    mysql> show table status;
    +-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
    | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment |
    +-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
    | gen_log_tbl | InnoDB | Dynamic | 5047150 | 145 | 735051776 | NULL | 403701760 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 6144 kB |
    | gen_trc_tbl | InnoDB | Dynamic | 19332362 | 1273 | 24616370176 | NULL | 800063488 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 6144 kB |
    +-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
    2 rows in set (1.64 sec)

    and then 10 or so minutes later :


    mysql> show table status;
    +-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
    | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment |
    +-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
    | gen_log_tbl | InnoDB | Dynamic | 5516838 | 135 | 745537536 | NULL | 408944640 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 8192 kB |
    | gen_trc_tbl | InnoDB | Dynamic | 14012450 | 1767 | 24769462272 | NULL | 811597824 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 8192 kB |
    +-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
    2 rows in set (1.95 sec)


    WHY does the number of ROWS decrease ???? and the InnoDB free :
    increase ???

    I am not doing any deletes from the tables - ONLY inserts....???

    mysql> select @@version;
    +-----------------+
    | @@VERSION |
    +-----------------+
    | 4.0.7-gamma-log |
    +-----------------+
    1 row in set (0.04 sec)

    Thanks

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimisation.

    For an exact count, you will need to do a count(*). However, this requires a full index scan.

  3. #3
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33
    thanks

Posting Permissions

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