Skip to Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from MySQL Stored Procedure Programming

Chapter 20. Basic SQL Tuning

In this chapter, we will tune simple SQL statements that may be included in MySQL stored programs. In particular, we'll optimize two of the most often executed SQL operations: retrieving data from a single table and joining two or more tables. Topics include:

  • How to determine when the use of an index is required to optimize a query

  • How to construct the best indexes to support specific queries

  • How MySQL chooses between available indexes, and how to direct MySQL to use a specific index if necessary

  • How to avoid "suppressing" an index

  • What to do when no index will suffice to optimize a query

  • How MySQL processes joins between multiple tables

  • How to create indexes that optimize table joins

  • How to determine the optimal join order and how to force MySQL to use a particular join order

Chapter 21 builds on these fundamentals, optimizing more complex SQL operations.

Examples in this chapter are based on tables created using the InnoDB storage engine. Although the same MySQL optimizer is used for all storage engines, you may observe different behaviors in other storage engines because of differences in optimizer statistics and indexing approaches.

Tuning Table Access

When retrieving data from a table, MySQL can basically follow one of two paths to locating the relevant rows:

  • Read every row in the table concerned (a full table scan), and return only those rows that match the WHERE clause criteria.

  • Use an index to find a subset of rows, and return the rows that match the ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

AirBnbBlueOriginElectronic ArtsHomeDepotNasdaqRakutenTata Consultancy Services

QuotationMarkO’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.
Julian F.
Head of Cybersecurity
QuotationMarkI wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.
Addison B.
Field Engineer
QuotationMarkI’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.
Amir M.
Data Platform Tech Lead
QuotationMarkI'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.
Mark W.
Embedded Software Engineer

You might also like

MySQL and JSON: A Practical Programming Guide

MySQL and JSON: A Practical Programming Guide

David Stokes
MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
MySQL Cookbook

MySQL Cookbook

Paul DuBois

Publisher Resources

ISBN: 0596100892Errata Page