O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required