The first principle for optimizing
INSERT statements is to optimize any
WHERE clause conditions used to
find the rows to be manipulated or inserted. The
UPDATE statements may contain
WHERE clauses, and the
INSERT statement may contain SQL that
defines the data to be inserted. Ensure that these
WHERE clauses are efficient—perhaps by
creating appropriate concatenated indexes .
The second principle for optimizing DML performance is to avoid creating too many indexes. Whenever a row is inserted or deleted, updates must occur to every index that exists against the table. These indexes exist to improve query performance, but bear in mind that each index also results in overhead when the row is created or deleted. For updates, only the indexes that reference the specific columns being modified need to be updated.
The MySQL language allows more than one row to be inserted in
INSERT operation. For
instance, the statement in Example 21-21 inserts five
rows into the
table in a single call.
INSERT INTO clickstream_log (url,timestamp,source_ip) values ('http://dev.mysql.com/downloads/mysql/5.0.html', '2005-02-10 11:46:23','192.168.34.87') , ('http://dev.mysql.com/downloads/mysql/4.1.html', '2005-02-10 11:46:24','192.168.35.78'), ('http://dev.mysql.com', '2005-02-10 11:46:24','192.168.35.90'), ('http://www.mysql.com/bugs', '2005-02-10 11:46:25','192.168.36.07'), ...