Home arrow MySQL arrow Enhancing MySQL Query Efficiency

Enhancing MySQL Query Efficiency

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

  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



13.3 General Query Enhancement

The way you write a query often affects how well indexes are used. Use the following principles to make your queries more efficient:

  • Don't refer to an indexed column within an expression that must be evaluated for every row in the table. Doing so prevents use of the index. Instead, isolate the column onto one side of a comparison when possible. For example, one way to select rows containing date values from the year 1994 and up is as follows:

    SELECT * FROM t WHERE YEAR(d) >= 1994;

    In this case, the value of YEAR(d) must be evaluated for every row in the table, so the index cannot be used. Instead, write the query like this:

    SELECT * FROM t WHERE d >= '1994-01-01';

    In the rewritten expression, the indexed column stands by itself on one side of the comparison and MySQL can apply the index to optimize the query.

    In situations like this, EXPLAIN is useful for verifying that one way of writing a query is better than another. For the two date-selection queries just shown, for example, you might find that EXPLAIN tells you something like this:

    >= 1994\G *********************** 1. row *************************** table: t type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 867038 Extra: Using where mysql> EXPLAIN SELECT * FROM t WHERE d >=
    '1994-01-01'\G *********************** 1. row *************************** table: t type: range possible_keys: d key: d key_len: 4 ref: NULL rows: 70968 Extra: Using where

    These results indicate that the second query is indeed better from the optimizer's point of view. MySQL can perform a range scan using the index for the column d, drastically reducing the number of rows that need to be examined. (The rows value drops from 867,038 to 70,968.)

  • When comparing an indexed column to a value, use a value that has the same datatype as the column. For example, you can look for rows containing a numeric id value of 18 with either of the following WHERE clauses:

    WHERE id = 18
    WHERE id = '18'

    MySQL will produce the same result either way, even though the value is specified as a number in one case and as a string in the other case. However, for the string value, MySQL must perform a string-to-number conversion, which might cause an index on the id column not to be used.

  • In certain cases, MySQL can use an index for pattern-matching operations performed with the LIKE operator. This is true if the pattern begins with a literal prefix value rather than with a wildcard character. An index on a name column can be used for a pattern match like this:

    WHERE name LIKE 'de%'

    That's because the pattern match is logically equivalent to a range search:

    WHERE name >= 'de' AND name < 'df'

    On the other hand, the following pattern makes LIKE more difficult for the optimizer:

    WHERE name LIKE '%de%'

    When a pattern starts with a wildcard character as just shown, MySQL cannot make efficient use of any indexes associated with that column. (Even if an index is used, the entire index must be scanned.)

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: