MySQL - INSERT queries taking long time

Question

I have a table with about 10.000 rows. Structure of the table is:

CREATE TABLE IF NOT EXISTS `demands` (
  `cycle_id` int(11) NOT NULL,
  `subject_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `market_id` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `currency_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `amount` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Keys:

• PRIMARY (cycle_id, subject_id, market_id, price)
• FK1 (market_id)
• FK2 (subject_id)
• FK3 (currency_id)

Query like this offen takes long time (about 1s):

INSERT INTO poptavky VALUES 
  (4, 'user', 17, 110, 'pound', 110) ,
  (4, 'user', 17, 90, 'pound', 120) , 
  (4, 'user', 17, 70, 'pound', 130) ; 

Where could be the problem? Thanks

Answer 1

It sounds like you are not on a very high end server and your problems are compounded by having far too many indexes.

This index on it's own is rather a massive one:

PRIMARY (cycle_id, subject_id, market_id, price)

It spans four columns and two of them are varchar columns. That in itself will not be a problem. but you have three more:

FK1 (market_id)
FK2 (subject_id)
FK3 (currency_id)

After each insert the database has to recalculate the indexes and that takes a lot of time when the number of indexes and/or index length is high. Particularly when lots of data is inserted all at once.