Home arrow MySQL arrow Optimizing for Query Speed

Optimizing for Query Speed

Optimzing your queries can help them run more efficiently, which can save a significant amount of time. This article covers index optimization and index usage. 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. Optimizing for Query Speed
  2. 13.1 Index Optimization and Index Usage
  3. 13.1.2 Obtaining Table Index Information
  4. 13.1.3 Using Indexes
  5. 13.1.3.1 Indexing Column Prefixes
  6. 13.1.3.2 Leftmost Index Prefixes
  7. 13.1.4 FULLTEXT Indexes
By: Sams Publishing
Rating: starstarstarstarstar / 25
August 03, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
Chapter 13: Optimizing for Query Speed

This chapter discusses general principles that are useful for optimizing queries to run more efficiently. It covers the following optimization strategies:

  • The primary optimization technique for reducing lookup times is to create good indexes. This is true not just for retrievals (SELECT statements); indexing reduces row lookup time for UPDATE and DELETE statements as well. You should know general principles for creating useful indexes and for avoiding unnecessary ones.

  • The EXPLAIN statement provides information about how the MySQL optimizer processes queries. This is of value when you're trying to determine how to make a query run better (for example, if you suspect indexes are not being used as you think they should be).

  • The way a query is written might prevent indexes from being used even if they are available. Rewriting the query often will allow the optimizer to use an index and process a query faster. Other times you can use query modifiers to give the scheduler a hint about how to execute a query.

  • In some cases, query processing for a task can be improved by using a different database design. This includes techniques such as choosing a storage engine with properties that best match application requirements and using summary tables.

Questions on the material in this chapter make up approximately 15% of the exam.

Why be concerned about optimization? The most obvious reason is to make your queries run faster. Another is that optimizing your queries helps everybody who uses the server, not just you. When the server runs more smoothly and does more with less work, it performs better as a whole:

  • A query that takes less time to run doesn't hold locks as long. Other clients that are waiting to update a table have to wait less time for a fast query than a slow one. This reduces the chance of a query backlog building up.

  • A query might be slow because it does not use indexes and therefore MySQL must scan a table in its entirety. For a large table, that involves a lot of processing and disk activity. This extra overhead affects not only your own query, it takes machine resources that could be devoted to processing other queries. Adding effective indexes allows MySQL to read only the relevant parts of the table, which is quicker and less disk intensive.

The optimization strategies covered here are guidelines known to result in generally improved query performance. However, you must test them in specific circumstances and measure the results, particularly if you can choose from more than one technique in a given situation.

The techniques discussed in this chapter can be used by any client application to improve how the queries it issues are executed by the server. Another approach to performance improvement is to reconfigure the server itself to change its overall operation. Server tuning is addressed in Chapter 16, "Advanced Server Features."



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