Question:
There is a need to execute 200,000 mysql insert queries per day. Now it takes about 5 hours. The script is located on the VPS. The system downloads lines from CSV daily. Is it possible to speed up the process?
Answer:
Depends on how the loading process takes place, but usually the problem of slow inserts is related to indexes, constraints and foreign keys on tables. The base needs to do additional checks to comply with all of these restrictions. Disabling autocommit (in the case of InnoDB), which will write to the binlog after each insert to ensure data safety, can also help.
General guidelines can be found here (InnoDB): https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
and here (MyISAM): https://dev.mysql.com/doc/refman/5.5/en/optimizing-myisam-bulk-data-loading.html