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.