mysql INNODB inserts very slow

The database's schema is as follows.

 CREATE  TABLE  `items` (  
 `id` mediumint( 8  )  unsigned NOT  NULL  AUTO_INCREMENT ,
 `name` varchar( 45  )  NOT  NULL ,
 `main_type` tinyint( 4  )  NOT  NULL ,

 `rarity` tinyint( 4  )  NOT  NULL ,
 `stack_size` smallint( 6  )  NOT  NULL ,
 `sub_type` tinyint( 4  )  NOT  NULL ,
 `cost` mediumint( 8  )  unsigned NOT  NULL ,
 `ilvl` smallint( 6  )  unsigned NOT  NULL DEFAULT  '0',
 `flavor_text` varchar( 250  )  NOT  NULL ,
 `rlvl` tinyint( 3  )  unsigned NOT  NULL ,
 `final` tinyint( 4  )  NOT  NULL DEFAULT  '0',
 PRIMARY  KEY (  `id`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = ascii;

Now, doing an insert on this table takes 0.22 seconds. I don't know why it's taking so long to do a single row insert. Reads are really really fast something like 0.005 seconds. With using the example configuration from here dev mysql innodb it averages ~0.002 to ~0.005 seconds. Why it takes more than 100x more time to do a single insert makes no sense to me. My computer is as follows. OS:Debian Sid x86-x64, Mysql 5.1, RAM:4GB ddr2, cpu 2.0Ghz dual core, HDD 7200RPM 32MB cache 640GB.

Why it's taking almost 100x as much time for a SELECT * FROM items; vs INSERT INTO items ...; will never make any sense to me. It's still a small table at only 70 rows, and took that long even when it had 0 rows.

Edit: Also this table has quite a few other tables linked to itself via the id. There's a few of them out there that are linked to it and do an on update=cascade; on delete=cascade;. I believe that that is the biggest issue here. If it is then, i can probably go in and change it and do individual deletes from the various little things when they are removed. The insert speed seems to be ~0.2 seconds whether i'm doing the insert on just items or i'm also doing it on another table that has a foreign key link to the main one.

Answer

Well, my first guess is that your InnoDB is probably broken. You can check whether there aren't any

• triggers that would do slow operation on insert


• processes going on that would lock the table


• foreign keys/constraints pointing to this table

Best way to completely audit a database against anything that would cause such trouble is to read schemas dump from mysqldump command.