insertion taking time in mysql 5.0.7



  • 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: LOAD DATA.

    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 KEY and 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 ALTER.

    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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2