optimization - MySQL - INSERT queries taking long time -
i have table 10.000 rows. structure of 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 offen takes long time (about 1s):
insert poptavky values (4, 'user', 17, 110, 'pound', 110) , (4, 'user', 17, 90, 'pound', 120) , (4, 'user', 17, 70, 'pound', 130) ;
where problem? thanks
it sounds not on high end server , problems compounded having far many indexes.
this index on it's own rather massive one:
primary (cycle_id, subject_id, market_id, price)
it spans 4 columns , 2 of them varchar columns. in not problem. have 3 more:
fk1 (market_id) fk2 (subject_id) fk3 (currency_id)
after each insert database has recalculate indexes , takes lot of time when number of indexes and/or index length high. particularly when lots of data inserted @ once.
Comments
Post a Comment