mysql INNODB inserts very slow
- by 133794m3r
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.
