Home arrow MySQL arrow Page 3 - Enhancing MySQL Query Efficiency

13.3.2 Optimizing Updates - MySQL

There are a number of things you can do in general to optimize your queries and make them more efficient. This article discusses several of these. It is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

TABLE OF CONTENTS:
  1. Enhancing MySQL Query Efficiency
  2. 13.3.1 Optimizing Queries by Limiting Output
  3. 13.3.2 Optimizing Updates
  4. 13.3.3 Using Scheduling Modifiers
By: Sams Publishing
Rating: starstarstarstarstar / 24
August 17, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The optimizations discussed so far have been shown for SELECT statements, but optimization techniques can be used for statements that update tables, too:

  • For a DELETE or UPDATE statement that uses a WHERE clause, try to write it in a way that allows an index to be used for determining which rows to delete or update. Techniques for this were discussed earlier for SELECT statements; they apply to DELETE or UPDATE as well.

  • EXPLAIN is used with SELECT queries, but you might also find it helpful for analyzing UPDATE and DELETE queries. Write a SELECT statement that has the same WHERE clause as the UPDATE or DELETE and analyze that.

  • Use multiple-row INSERT statements instead of multiple single-row INSERT statements. For example, instead of using three single-row statements like this:

    mysql> INSERT INTO t (id, name)
    VALUES(1,'Bea');
    mysql> INSERT INTO t (id, name)
    VALUES(2,'Belle');
    mysql> INSERT INTO t (id, name)
    VALUES(3,'Bernice');

    You could use a single multiple-row statement that does the same thing:

    mysql> INSERT INTO t (id, name)
    VALUES(1,'Bea'),(2,'Belle'),(3,'Bernice');

    The multiple-row statement is shorter, which is less information to send to the server. More important, it allows the server to perform all the updates at once and flush the index a single time, rather than after each of the individual inserts. This optimization can be used with any storage engine.

    If you're using an InnoDB table, you can get better performance even for single-row statements by grouping them within a transaction rather than by executing them with autocommit mode enabled:

    mysql> BEGIN;
    mysql> INSERT INTO t (id, name)
    VALUES(1,'Bea');
    mysql> INSERT INTO t (id, name)
    VALUES(2,'Belle');
    mysql> INSERT INTO t (id, name)
    VALUES(3,'Bernice');
    mysql> COMMIT;

    Using a transaction allows InnoDB to flush the changes at commit time. In autocommit mode, InnoDB flushes the changes after each insert.

  • For any storage engine, LOAD DATA INFILE is even faster than multiple-row INSERT statements. For MyISAM in particular, if you're loading an empty table, MySQL will even automatically disable updating nonunique indexes during the load operation to speed it up more.

  • To replace existing rows, use REPLACE rather than DELETE plus INSERT.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: