mysql innodb insert statement takes very long time

Question

i have innodb tables (all of them are in the same file, and this file became very large, about 8GB) when i insert a row in one this table (this table contains about 1 million record), this insert statement takes about 30 seconds !!! is there anyway to improve this issue?? any idea is highly appreciated

CREATE TABLE userdata ( userno blob NOT NULL, amount double(11,2), active enum('0','1') default '0', used enum('0','1') default '0', ActionTime timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, Bonus enum('0','1') default '0', UserType enum('Perm','Temp') NOT NULL default 'Perm', serial_number bigint(20) NOT NULL default '0', PRIMARY KEY (userno(50)), KEY AIX (active), KEY UIX (used), KEY Id (userno(20),active), KEY UI (userno(50)), KEY SNI (serial_number) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Сould you please post the table's definition? Are there indexes, foreign key constraints and triggers on the table? Please run SHOW CREATE TABLE mytable and post its output. — Quassnoi
– Quassnoi, Commented Nov 2, 2010 at 10:29
Also look at Hardware part. Can you try increasing the RAM? are there any other CPU intensive processes running the server? If so, can you move these CPU intensive processes onto another server? How many users are currently accessing the DB? — PradeepGB
– PradeepGB, Commented Nov 2, 2010 at 10:43
CREATE TABLE userdata ( userno blob NOT NULL, amount double(11,2), active enum('0','1') default '0', used enum('0','1') default '0', ActionTime timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, Bonus enum('0','1') default '0', UserType enum('Perm','Temp') NOT NULL default 'Perm', serial_number bigint(20) NOT NULL default '0', PRIMARY KEY (userno(50)), KEY AIX (active), KEY UIX (used), KEY Id (userno(20),active), KEY UI (userno(50)), KEY SNI (serial_number) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 — Alaa
– Alaa, Commented Nov 2, 2010 at 10:55

Quassnoi · Accepted Answer · 2010-11-02 10:58:22Z

1

Having BLOB as a PRIMARY KEY is not the best idea.

Could you change its datatype to INT?

answered Nov 2, 2010 at 10:58

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alaa Over a year ago

i think so,,,, i am trying to get as much ideas as possible

Alaa Over a year ago

i got involved in this system and i have to fix this problem

Jaydee Over a year ago

I feel your pain, you should have run when you had the chance:-)

Collectives™ on Stack Overflow

mysql innodb insert statement takes very long time

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related