insertion taking time in mysql 5.0.7
Alberto last edited by
Tables having 70-80 columns and 15-20 indexes.
Rows around 90+ lac
Taking time between 60 to 300 seconds in insertion while 30 columns are in a single query.
Insertion from the application one by one
Please suggest how to resolve this issue.
Fastest way if the data comes from a CSV file:
Next best: "batch"
INSERTs-- Load 100 to 1000 rows at a time. (With 9 million rows, that will be thousands of batches, but that is OK.)
If the data can be pre-sorted, sort by the
PRIMARY KEYand have the PK already included in the table definition.
Depending on various factors, it may be faster to drop (or not have) the secondary keys, load the data, then add the secondary keys in a single
MySQL 5.0 was released in 2005; please consider upgrading. Still, my answer probably applies to all versions of MySQL and MariaDB and to MyISAM and InnoDB.