In this chapter we examined some of the basic principles for tuning simple SQL statements. Tuning SQL inside of MySQL stored programs is probably the single most important thing we can do to avoid poorly performing stored programs.

For SQL statements that retrieve a small proportion of the rows from a table (say, 5 to 15%), you will probably want to create indexes to obtain good performance. Here are some best practice guidelines for creating indexes:

  • Create concatenated indexes that include all of the columns referenced in the WHERE clause.
  • Consider adding additional columns that appear in the SELECT list to allow for an "index only" access path.
  • Create concatenated indexes to support the widest possible range of queriesconcatenated indexes can be used for queries that reference only a subset of the columns in the index, provided that the "leading" columns are in the WHERE clause. This means that you should put the most commonly used columns first in the index.
  • If the number of concatenated indexes needed to support all possible queries is too large (say five or more), create single-column indexes on selective columns that MySQL can merge.

MySQL can join large tables effectively only if an index exists on the join columns for at least one of the tables being joined. To optimize basic joins:

  • Create a concatenated index on all of the columns used to join the two tables.
  • Make sure that any WHERE clause conditions are executed before the tables are joined. That is, the "driving table" should be the table that has the most selective condition in the WHERE clause. This will create the most efficient join.
  • Provided that joins are supported by indexes and that WHERE clause conditions are processed in the first few tables to be joined, be aware that the best join order will be from smallest table to largest table.

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions


Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development

MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208 © 2008-2020.
If you may any questions please contact us: