Re: Very slow insert with Innodb
Posted by:
Vikram Rao
Date: January 07, 2009 03:37AM
+-----------------------+---------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+-------------------+-------+
| ID | bigint(16) unsigned | NO | PRI | NULL | |
| Fld1 | bigint(8) unsigned | YES | MUL | NULL | |
| Fld2 | bigint(8) unsigned | YES | MUL | NULL | |
| Fld3 | bigint(8) unsigned | YES | MUL | NULL | |
| Fld4 | char(1) | YES | | 0 | |
| Date | timestamp | NO | | CURRENT_TIMESTAMP | |
| Fld5 | bigint(8) | NO | | NULL | |
| Fld6 | tinyint(2) | NO | | 0 | |
| Fld7 | tinyint(2) | YES | | NULL | |
+-----------------------+---------------------+------+-----+-------------------+-------+
CREATE TABLE `Tbl1` (
`ID` bigint(16) unsigned NOT NULL,
`Fld1` bigint(8) unsigned default NULL,
`Fld2` bigint(8) unsigned default NULL,
`Fld3` bigint(8) unsigned default NULL,
`Fld4` char(1) default '0',
`Date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`Fld5` bigint(8) NOT NULL,
`Fld6` tinyint(2) NOT NULL default '0',
`Fld7` tinyint(2) default NULL,
PRIMARY KEY (`PurchasedGameTicketID`),
KEY `Fld1Tbl1` (`Fld1`),
KEY `Fld2Tbl1` (`Fld2`),
KEY `Fld3Tbl1` (`Fld3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
These queries would run on these tables from multiple clients...
select * from Tbl1 where Fld1 = ? and Fld2 = ? and Fld6 = ?
insert into Tbl1 (ID,Fld1,Fld2,Fld3,Fld4,Fld5,Fld6,Fld7) values (,,,,,,,),(,,,,,,,),(,,,,,,,),(,,,,,,,),(,,,,,,,),(,,,,,,,)(,,,,,,,)