Home arrow MySQL arrow Page 2 - SQL Performance and Tuning Considerations

Design the Tables Efficiently - MySQL

This article, the first of two parts, will show you how to make the SQL statements you write run faster and more efficiently. It is excerpted from chapter 11 of the book SQL DeMYSTiFied, written by Andy Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).

TABLE OF CONTENTS:
  1. SQL Performance and Tuning Considerations
  2. Design the Tables Efficiently
  3. General RDBMS Considerations
  4. Efficient Query Design
  5. Use Indexes Wisely
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 23
March 16, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Relational table design can have a huge effect on performance. Most popular RDBMSs have manuals or white papers that cover best practices for designing efficient tables. Here are some general guidelines that apply to all SQL implementations:

  • CHAR vs. VARCHAR  For character columns that are five characters or less, the CHAR data type is most appropriate, even if the data length varies. The VARCHAR data type is better for variable length columns that are usually longer than five characters in size. For columns that always contain data of the same length, CHAR is an obvious choice. The reason for using CHAR for short columns is that VARCHAR columns include 1 to 3 bytes to hold the length of the column data, and there is additional processing overhead for calculating the length when the column data changes.
  • Numeric column data types  Use the smallest data type in which the data will fit. A lot of space is wasted if, for example, the BIGINT data type is used when the data would always fit in INT or SMALLINT.
  • Identical data types for primary and foreign key columns  Joins work much more efficiently if the data types of the columns used in the join predicate (usually the primary key in one table and a foreign key in the other table) have identical data types. When the data types are different, the DBMS has to convert one of them so that the data values can be compared correctly, and such conversion amounts to unnecessary overhead.
  • Beware of triggers  Triggers placed on database tables are sometimes convenient and/or necessary ways to solve specific problems such as enforcing complicated constraints. However, the work that the trigger does is part of the unit of work of the SQL statement that modifies the table (the statement that caused the trigger to fire), and therefore, it slows down that SQL statement.

Tuning SQL Queries

About 80 percent of database query performance problems can be solved by adjusting the SQL statement. However, you must understand how the particular DBMS being used processes SQL statements in order to know what to tweak. For example, placing SQL statements inside stored procedures can yield remarkable performance improvement in Microsoft SQL Server and Sybase, but the same is not true in Oracle.

A query execution plan is a description of how an RDBMS will process a particular query, including index usage, join logic, and estimated resource cost. It is important to learn how to use the "explain plan" utility in your DBMS, if one is available, because it will show you exactly how the DBMS will process the SQL statement you are attempting to tune. Examples of obtaining execution plans for Oracle and Microsoft SQL Server databases appear later in this chapter.

The next section covers some general query design and tuning tips for SQL, followed by sections that offer recommendations for several of the most popular RDBMS products currently on the market.



 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

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: