It seems that when your index is PRIMARY on InnoDB tables, it's magic and is part of the data thereby it is not included in the index_length field.
I have never noticed this. I don't think adding a new index will make a difference. You could try moving your log files to a different disk array than where your data is. If you have binary and query logging enabled, it's probably a good idea. ------------------------------ *Johnny Withers* jwith...@ecashsoftware.com 601.919.2275 x112 [image: eCash Software Systems] On Fri, Sep 3, 2010 at 12:45 PM, Alexandre Vieira <nul...@gmail.com> wrote: > Hi, > > When creating a table in MySQL with a PK it automatically creates an INDEX, > correct? > > The Index_Length: 0 is rather strange..I've created a new INDEX on top of > my PK column on my test system and Index_Length shows a big value different > from 0. Do you think this might have any impact? > > mysql> show index from gwtraffic.clientinfo; > > > +------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | > > +------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | clientinfo | 0 | PRIMARY | 1 | userid | > A | 548216 | NULL | NULL | | BTREE | | > | clientinfo | 1 | userid_idx | 1 | userid | > A | 548216 | NULL | NULL | | BTREE | | > > +------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > > 2 rows in set (0.01 sec) > > mysql> show table status LIKE 'clientinfo'; > > > +------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------+ > | Name | Engine | Version | Row_format | Rows | Avg_row_length | > Data_length | Max_data_length | Index_length | Data_free | Auto_increment | > Create_time | Update_time | Check_time | Collation | > Checksum | Create_options | Comment | > > +------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------+ > | clientinfo | InnoDB | 10 | Compact | 548216 | 62 | > 34144256 | 0 | 5783552 | 0 | NULL | > 2010-09-03 17:38:16 | NULL | NULL | latin1_swedish_ci | > NULL | | InnoDB free: 1214464 kB | > > > +------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------+ > 1 row in set (0.00 sec) > > I'm trying to stress my test DB but can't measure any different results > with or without the second INDEX. > > Regarding the disks.. the DB is updated 20+ times every second. Writing the > log, checkpoint to disk, etc.. can cause that much load? > > BR > AJ > > >
