(mysql.info) insert-speed

(mysql.info) how-to-avoid-table-scan
 
 7.2.16 Speed of `INSERT' Statements
 -----------------------------------
 
 The time required for inserting a row is determined by the following
 factors, where the numbers indicate approximate proportions:
 
    * Connecting: (3)
 
    * Sending query to server: (2)
 
    * Parsing query: (2)
 
    * Inserting row: (1 × size of row)
 
    * Inserting indexes: (1 × number of indexes)
 
    * Closing: (1)
 
 This does not take into consideration the initial overhead to open
 tables, which is done once for each concurrently running query.
 
 The size of the table slows down the insertion of indexes by log N,
 assuming B-tree indexes.
 
 You can use the following methods to speed up inserts:
 
    * If you are inserting many rows from the same client at the same
      time, use `INSERT' statements with multiple `VALUES' lists to
      insert several rows at a time. This is considerably faster (many
      times faster in some cases) than using separate single-row
      `INSERT' statements. If you are adding data to a non-empty table,
      you can tune the `bulk_insert_buffer_size' variable to make data
      insertion even faster. See  server-system-variables.
 
    * If you are inserting a lot of rows from different clients, you can
      get higher speed by using the `INSERT DELAYED' statement. See
       insert-delayed.
 
    * For a `MyISAM' table, you can use concurrent inserts to add rows
      at the same time that `SELECT' statements are running if there are
      no deleted rows in middle of the table. See 
      concurrent-inserts.
 
    * When loading a table from a text file, use `LOAD DATA INFILE'.
      This is usually 20 times faster than using `INSERT' statements. See
       load-data.
 
    * With some extra work, it is possible to make `LOAD DATA INFILE'
      run even faster for a `MyISAM' table when the table has many
      indexes. Use the following procedure:
 
        1. Optionally create the table with `CREATE TABLE'.
 
        2. Execute a `FLUSH TABLES' statement or a `mysqladmin
           flush-tables' command.
 
        3. Use `myisamchk --keys-used=0 -rq /PATH/TO/DB/TBL_NAME.' This
           removes all use of indexes for the table.
 
        4. Insert data into the table with `LOAD DATA INFILE'. This does
           not update any indexes and therefore is very fast.
 
        5. If you intend only to read from the table in the future, use
           `myisampack' to compress it. See  compressed-format.
 
        6. Re-create the indexes with `myisamchk -rq
           /PATH/TO/DB/TBL_NAME'.  This creates the index tree in memory
           before writing it to disk, which is much faster that updating
           the index during `LOAD DATA INFILE' because it avoids lots of
           disk seeks. The resulting index tree is also perfectly
           balanced.
 
        7. Execute a `FLUSH TABLES' statement or a `mysqladmin
           flush-tables' command.
 
      Note that `LOAD DATA INFILE' performs the preceding optimization
      automatically if the `MyISAM' table into which you insert data is
      empty. The main difference is that you can let `myisamchk'
      allocate much more temporary memory for the index creation than
      you might want the server to allocate for index re-creation when
      it executes the `LOAD DATA INFILE' statement.
 
      You can also disable or enable the indexes for a `MyISAM' table by
      using the following statements rather than `myisamchk'. If you use
      these statements, you can skip the `FLUSH TABLE' operations:
 
           ALTER TABLE TBL_NAME DISABLE KEYS;
           ALTER TABLE TBL_NAME ENABLE KEYS;
 
    * To speed up `INSERT' operations that are performed with multiple
      statements, lock your tables:
 
           LOCK TABLES a WRITE;
           INSERT INTO a VALUES (1,23),(2,34),(4,33);
           INSERT INTO a VALUES (8,26),(6,29);
           UNLOCK TABLES;
 
      This benefits performance because the index buffer is flushed to
      disk only once, after all `INSERT' statements have completed.
      Normally, there would be as many index buffer flushes as there are
      `INSERT' statements. Explicit locking statements are not needed if
      you can insert all rows with a single `INSERT'.
 
      For transactional tables, you should use `START TRANSACTION' and
      `COMMIT' instead of `LOCK TABLES' to obtain faster insertions.
 
      Locking also lowers the total time for multiple-connection tests,
      although the maximum wait time for individual connections might go
      up because they wait for locks. For example:
 
        1. Connection 1 does 1000 inserts
 
        2. Connections 2, 3, and 4 do 1 insert
 
        3. Connection 5 does 1000 inserts
 
      If you do not use locking, connections 2, 3, and 4 finish before 1
      and 5. If you use locking, connections 2, 3, and 4 probably do not
      finish before 1 or 5, but the total time should be about 40%
      faster.
 
      `INSERT', `UPDATE', and `DELETE' operations are very fast in MySQL,
      but you can obtain better overall performance by adding locks
      around everything that does more than about five inserts or
      updates in a row. If you do very many inserts in a row, you could
      do a `LOCK TABLES' followed by an `UNLOCK TABLES' once in a while
      (each 1,000 rows or so) to allow other threads access to the
      table. This would still result in a nice performance gain.
 
      `INSERT' is still much slower for loading data than `LOAD DATA
      INFILE', even when using the strategies just outlined.
 
    * To increase performance for `MyISAM' tables, for both `LOAD DATA
      INFILE' and `INSERT', enlarge the key cache by increasing the
      `key_buffer_size' system variable. See  server-parameters.
Info Catalog
(mysql.info) how-to-avoid-table-scan
(mysql.info) query-speed
(mysql.info) update-speed
automatically generated byinfo2html