Home arrow MySQL arrow Page 2 - Enhancing MySQL Query Efficiency

13.3.1 Optimizing Queries by Limiting Output - 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

Some optimizations can be done independently of whether indexes are used. A simple but effective technique is to reduce the amount of output a query produces.

One way to eliminate unnecessary output is by using a LIMIT clause. If you don't need the entire result set, specify how many rows the server should return by including LIMIT in your query. This helps two ways:

  • Less information need be returned over the network to the client.

  • In many cases, LIMIT allows the server to terminate query processing earlier than it would otherwise.

Another way to limit query output is by selecting only the columns you need, rather than using SELECT * to retrieve all columns. Suppose you want information about countries having names that begin with M. The following query produces that information:

SELECT * FROM Country WHERE Name LIKE 'M%';

However, if all you really want to know is the country names, don't write the query like that. Most of the information retrieved will be irrelevant to what you want to know, resulting in unnecessary server effort and network traffic. Instead, select only the Name column:

SELECT Name FROM Country WHERE Name LIKE 'M%';

The second query is faster because MySQL has to return less information to the client when you select just one column rather than all of them.

In addition, if an index on Name exists, you get even more improvement for two reasons:

  • The index can be used to determine quickly which Name values satisfy the condition in the WHERE clause. This is faster than scanning the entire table.

  • Depending on the storage engine, the server might not read the table rows at all. If the values requested by the query are in the index, by reading the index, MySQL already has the information that the client wants. For example, the MyISAM handler will read the index file to determine which values satisfy the query, and then return them to the client without reading the datafile at all. Doing so is faster than reading both the index file and the datafile.



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