MySQL Stored Procedure Programming

Tuning DML (INSERT, UPDATE, DELETE)

The first principle for optimizing UPDATE, DELETE, and INSERT statements is to optimize any WHERE clause conditions used to find the rows to be manipulated or inserted. The DELETE and 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.

Batching Inserts

The MySQL language allows more than one row to be inserted in a single INSERT operation. For instance, the statement in Example 21-21 inserts five rows into the clickstream_log table in a single call.

Example 21-21. Batch INSERT statement

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'), ...