book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein

March 2006

Intermediate to advanced

640 pages

17h 8m

English

O'Reilly Media, Inc.

Read now

Unlock full access

MySQL Stored Procedure Programming
SPECIAL OFFER: Upgrade this ebook with O’Reilly
A Note Regarding Supplemental Files
Advance Praise for MySQL Stored Procedure Programming
Preface
Objectives of This Book
Structure of This Book
What This Book Does Not Cover
Conventions Used in This Book
Which Version?
Resources Available at the Book's Web Site

Using Code Examples
Safari® Enabled
How to Contact Us
Acknowledgments
I. Stored Programming Fundamentals
1. Introduction to MySQL Stored Programs
1.1. What Is a Stored Program?1.1.1. Why Use Stored Programs?1.1.2. A Brief History of MySQL1.1.3. MySQL Stored Procedures, Functions, and Triggers
1.2. A Quick Tour
1.2.1. Integration with SQL1.2.2. Control and Conditional Logic1.2.3. Stored Functions1.2.4. When Things Go Wrong1.2.5. Triggers
1.3. Resources for Developers Using Stored Programs
1.3.1. Books1.3.2. Internet Resources
1.4. Some Words of Advice for Developers
1.4.1. Don't Be in Such a Hurry!1.4.2. Don't Be Afraid to Ask for Help1.4.3. Take a Creative, Even Radical Approach
1.5. Conclusion
2. MySQL Stored Programming Tutorial
2.1. What You Will Need
2.2. Our First Stored Procedure
2.2.1. Creating the Procedure2.2.2. Creating the Procedure Using the MySQL Query Browser
2.3. Variables
2.4. Parameters
2.4.1. Parameter Modes
2.5. Conditional Execution
2.6. Loops
2.7. Dealing with Errors
2.8. Interacting with the Database
2.8.1. SELECTing INTO Local Variables2.8.2. Using Cursors2.8.3. Returning Result Sets from Stored Procedures2.8.4. Embedding Non-SELECTs
2.9. Calling Stored Programs from Stored Programs
2.10. Putting It All Together
2.11. Stored Functions
2.12. Triggers
2.13. Calling a Stored Procedure from PHP
2.14. Conclusion
3. Language Fundamentals
3.1. Variables, Literals, Parameters, and Comments3.1.1. Variables3.1.2. Literals3.1.3. Rules for Variable Names3.1.4. Assigning Values to Variables3.1.5. Parameters3.1.6. User Variables3.1.7. Comments
3.2. Operators
3.2.1. Mathematical Operators3.2.2. Comparison Operators3.2.3. Logical Operators3.2.4. Bitwise Operators
3.3. Expressions
3.4. Built-in Functions
3.5. Data Types
3.5.1. String Data Types3.5.1.1. The ENUM data type3.5.1.2. The SET data type3.5.2. Numeric Data Types3.5.3. Date and Time Data Types3.5.4. TEXT and BLOB Data Types
3.6. MySQL 5 "Strict" Mode
3.6.1. Stored Program Behavior and Strict Mode3.6.2. Program Examples
3.7. Conclusion
4. Blocks, Conditional Statements, and Iterative Programming
4.1. Block Structure of Stored Programs4.1.1. Structure of a Block4.1.2. Nested Blocks
4.2. Conditional Control
4.2.1. The IF Statement4.2.1.1. TRUE or FALSE (or neither)?4.2.1.2. Simple IF-THEN combinations4.2.1.3. IF-THEN-ELSE statements4.2.1.4. IF-THEN-ELSEIF-ELSE statements4.2.2. The CASE Statement4.2.2.1. Simple CASE statement4.2.2.2. "Searched" CASE statement4.2.3. IF Versus CASE
4.3. Iterative Processing with Loops
4.3.1. LOOP Statement4.3.2. LEAVE Statement4.3.3. ITERATE Statement4.3.4. REPEAT ... UNTIL Loop4.3.5. WHILE Loop4.3.6. Nested Loops4.3.7. Parting Comments on Loops
4.4. Conclusion
5. Using SQL in Stored Programming
5.1. Using Non-SELECT SQL in Stored Programs
5.2. Using SELECT Statements with an INTO Clause
5.3. Creating and Using Cursors
5.3.1. Defining a Cursor5.3.2. Cursor Statements5.3.3. Fetching a Single Row from a Cursor5.3.4. Fetching an Entire Result Set5.3.5. Types of Cursor Loops5.3.6. Nested Cursor Loops5.3.7. Exiting the Cursor Loop Prematurely5.3.8. Cursor Error Conditions
5.4. Using Unbounded SELECT Statements
5.4.1. Retrieving the Result Sets in the Calling Program5.4.2. Returning Result Sets to Another Stored Procedure
5.5. Performing Dynamic SQL with Prepared Statements
5.6. Handling SQL Errors: A Preview
5.7. Conclusion
6. Error Handling
6.1. Introduction to Error Handling6.1.1. A Simple First Example6.1.2. Handling Last Row Conditions
6.2. Condition Handlers
6.2.1. Types of Handlers6.2.2. Handler Conditions6.2.3. Handler Examples6.2.4. Handler Precedence6.2.5. Scope of Condition Handlers
6.3. Named Conditions
6.4. Missing SQL:2003 Features
6.4.1. Directly Accessing SQLCODE or SQLSTATE6.4.2. Creating Your Own Exceptions with the SIGNAL Statement6.4.3. Emulating the SIGNAL Statement
6.5. Putting It All Together
6.6. Handling Stored Program Errors in the Calling Application
6.6.1. PHP6.6.2. Perl6.6.3. Java/JDBC6.6.4. Python6.6.5. C# .NET6.6.6. Visual Basic .NET
6.7. Conclusion
II. Stored Program Construction
7. Creating and Maintaining Stored Programs
7.1. Creating Stored Programs7.1.1. Editing Stored Programs Using a System Editor7.1.2. Using the MySQL Query Browser7.1.3. Using Third-Party Tools7.1.4. Handling Semicolons in Stored Program Code
7.2. Editing an Existing Stored Program
7.2.1. Editing a Program in Place7.2.2. Maintaining Stored Programs in External Files
7.3. SQL Statements for Managing Stored Programs
7.3.1. CREATE PROCEDURE7.3.2. CREATE FUNCTION7.3.3. CREATE TRIGGER7.3.4. ALTER PROCEDURE/FUNCTION7.3.5. DROP PROCEDURE/FUNCTION/TRIGGER
7.4. Getting Information About Stored Programs
7.4.1. SHOW PROCEDURE/FUNCTION STATUS7.4.2. SHOW CREATE PROCEDURE/FUNCTION7.4.3. INFORMATION_SCHEMA.ROUTINES Table7.4.4. INFORMATION_SCHEMA.TRIGGERS Table
7.5. Conclusion
8. Transaction Management
8.1. Transactional Support in MySQL8.1.1. Isolation Levels8.1.2. Transaction Management Statements
8.2. Defining a Transaction
8.3. Working with Savepoints
8.4. Transactions and Locks
8.4.1. Situations in Which Locks Arise8.4.2. Deadlocks8.4.3. Lock Timeouts8.4.4. Optimistic and Pessimistic Locking Strategies8.4.4.1. Pessimistic locking strategy8.4.4.2. Optimistic locking strategy8.4.4.3. Choosing between strategies
8.5. Transaction Design Guidelines
8.6. Conclusion
9. MySQL Built-in Functions
9.1. String Functions9.1.1. ASCII9.1.2. CHAR9.1.3. CHARSET9.1.4. CONCAT9.1.5. CONCAT_WS9.1.6. INSERT9.1.7. INSTR9.1.8. LCASE9.1.9. LEFT9.1.10. LENGTH9.1.11. LOAD_FILE9.1.12. LOCATE9.1.13. LPAD9.1.14. LTRIM9.1.15. REPEAT9.1.16. REPLACE9.1.17. RPAD9.1.18. RTRIM9.1.19. STRCMP9.1.20. SUBSTRING9.1.21. TRIM9.1.22. UCASE9.1.23. Other String Functions
9.2. Numeric Functions
9.2.1. ABS9.2.2. BIN9.2.3. CEILING9.2.4. CONV9.2.5. FLOOR9.2.6. FORMAT9.2.7. HEX9.2.8. LEAST9.2.9. MOD9.2.10. POWER9.2.11. RAND9.2.12. ROUND9.2.13. SIGN9.2.14. SQRT9.2.15. Other Numeric Functions
9.3. Date and Time Functions
9.3.1. ADDTIME9.3.2. CONVERT_TZ9.3.3. CURRENT_DATE9.3.4. CURRENT_TIME9.3.5. CURRENT_TIMESTAMP9.3.6. DATE9.3.7. DATE_ADD9.3.8. DATE_FORMAT9.3.9. DATE_SUB9.3.10. DATEDIFF9.3.11. DAY9.3.12. DAYNAME9.3.13. DAYOFWEEK9.3.14. DAYOFYEAR9.3.15. EXTRACT9.3.16. GET_FORMAT9.3.17. MAKEDATE9.3.18. MAKETIME9.3.19. MONTHNAME9.3.20. NOW9.3.21. SEC_TO_TIME9.3.22. STR_TO_DATE9.3.23. TIME_TO_SEC9.3.24. TIMEDIFF9.3.25. TIMESTAMP9.3.26. TIMESTAMPADD9.3.27. TIMESTAMPDIFF9.3.28. WEEK9.3.29. WEEKDAY9.3.30. YEAR9.3.31. YEARWEEK9.3.32. Other Date and Time Functions
9.4. Other Functions
9.4.1. BENCHMARK9.4.2. COALESCE9.4.3. CURRENT_USER9.4.4. DATABASE9.4.5. GET_LOCK9.4.6. IFNULL9.4.7. INTERVAL9.4.8. IS_FREE_LOCK9.4.9. ISNULL9.4.10. NULLIF9.4.11. RELEASE_LOCK9.4.12. SESSION_USER9.4.13. SYSTEM_USER9.4.14. USER9.4.15. UUID9.4.16. VERSION
9.5. Conclusion
10. Stored Functions
10.1. Creating Stored Functions10.1.1. The RETURN Statement10.1.2. Parameters to Stored Functions10.1.3. The DETERMINISTIC and SQL Clauses
10.2. SQL Statements in Stored Functions
10.3. Calling Stored Functions
10.4. Using Stored Functions in SQL
10.4.1. Using SQL in Stored Functions
10.5. Conclusion
11. Triggers
11.1. Creating Triggers11.1.1. Referring to Column Values Within the Trigger11.1.2. Triggering Actions11.1.3. BEFORE and AFTER Triggers
11.2. Using Triggers
11.2.1. Maintaining Derived Data11.2.2. Implementing Logging11.2.3. Validating Data with Triggers
11.3. Trigger Overhead
11.4. Conclusion
III. Using MySQL Stored Programs in Applications
12. Using MySQL Stored Programs in Applications
12.1. The Pros and Cons of Stored Programs in Modern Applications
12.2. Advantages of Stored Programs
12.2.1. They Enhance Database Security12.2.2. They Provide a Mechanism for Data Abstraction12.2.3. They Reduce Network Traffic12.2.4. They Allow for Common Routines Across Multiple Application Types12.2.5. They Facilitate Division of Duties12.2.6. They May Provide Portability
12.3. Disadvantages of Stored Programs
12.3.1. They Can Be Computationally Inferior12.3.2. They Can Lead to Logic Fragmentation12.3.3. They Do Not Provide Portability
12.4. Calling Stored Programs from Application Code
12.4.1. Preparing a Stored Program Call for Execution12.4.2. Registering Parameters12.4.3. Setting Output Parameters12.4.4. Executing the Stored Program12.4.5. Retrieving Result Sets12.4.6. Retrieving Output Parameters12.4.7. Closing or Re-Executing the Stored Program12.4.8. Calling Stored Functions
12.5. Conclusion
13. Using MySQL Stored Programs with PHP
13.1. Options for Using MySQL with PHP
13.2. Using PHP with the mysqli Extension
13.2.1. Enabling the mysqli Extension13.2.2. Connecting to MySQL13.2.3. Checking for Errors13.2.4. Executing a Simple Non-SELECT Statement13.2.5. Retrieving a Result Set13.2.6. Managing Transactions13.2.7. Using Prepared Statements13.2.8. Retrieving Result Sets from Prepared Statements13.2.9. Getting Result Set Metadata13.2.10. Processing a Dynamic Result Set13.2.11. Calling Stored Programs with mysqli13.2.12. Handling Output Parameters13.2.13. Retrieving Multiple Result Sets
13.3. Using MySQL with PHP Data Objects
13.3.1. Connecting to MySQL13.3.2. Executing a Simple Non-SELECT Statement13.3.3. Catching Errors13.3.4. Managing Transactions13.3.5. Issuing a One-Off Query13.3.6. Using Prepared Statements13.3.7. Binding Parameters to a Prepared Statement13.3.8. Getting Result Set Metadata13.3.9. Processing a Dynamic Result Set13.3.10. Calling Stored Programs with PDO13.3.11. Binding Input Parameters to Stored Programs13.3.12. Handling Multiple Result Sets13.3.13. Handling Output Parameters13.3.14. A Complete Example
13.4. Conclusion
14. Using MySQL Stored Programs with Java
14.1. Review of JDBC Basics14.1.1. Installing the Driver and Configuring Your IDE14.1.2. Registering the Driver and Connecting to MySQL14.1.3. Issuing a Non-SELECT Statement14.1.4. Issuing a SELECT and Retrieving a Result Set14.1.5. Getting Result Set Metadata14.1.6. Using Prepared Statements14.1.7. Handling Transactions14.1.8. Handling Errors
14.2. Using Stored Programs in JDBC
14.2.1. Using the CallableStatement Interface14.2.2. Registering OUT Variables14.2.3. Supplying Input Parameters14.2.4. Executing the Procedure14.2.5. Retrieving a Result Set14.2.6. Retrieving Multiple Result Sets14.2.7. Dynamically Processing Result Sets14.2.8. Retrieving Output Parameter Values
14.3. Stored Programs and J2EE Applications
14.3.1. Using Stored Programs Within Java Servlets14.3.2. Using Stored Programs from EJB
14.4. Using Stored Procedures with Hibernate
14.4.1. Hibernate Support for MySQL Stored Procedures14.4.2. Using a Stored Procedure to Load an Object14.4.3. Hibernate Queries14.4.4. Using Stored Procedures for Persistence
14.5. Using Stored Procedures with Spring
14.6. Conclusion
15. Using MySQL Stored Programs with Perl
15.1. Review of Perl DBD::mysql Basics15.1.1. Installing DBD::mysql15.1.1.1. Installing DBD::mysql on Linux or Unix15.1.1.2. Installing DBD::mysql on Windows15.1.2. Connecting to MySQL15.1.2.1. Connection attributes15.1.3. Handling Errors15.1.4. Issuing a Simple One-off Statement15.1.5. Preparing a Statement for Reuse15.1.6. Using Bind Variables15.1.7. Issuing a Query and Retrieving Results15.1.8. There's More Than One Way To Do It15.1.8.1. fetchrow_arrayref method15.1.8.2. fetchrow_hashref method15.1.8.3. fetchall_arrayref method15.1.8.4. dump_results method15.1.8.5. bind_col and fetch methods15.1.9. Getting Result Set Metadata15.1.10. Performing Transaction Management
15.2. Executing Stored Programs with DBD::mysql
15.2.1. Handling Multiple Result Sets15.2.2. Handling Dynamic Result Sets15.2.3. Handling Output Variables15.2.4. A Complete Example
15.3. Conclusion
16. Using MySQL Stored Programs with Python
16.1. Installing the MySQLdb Extension
16.2. MySQLdb Basics
16.2.1. Creating a Connection16.2.2. Handling Exceptions16.2.3. Executing a Simple Statement16.2.4. Passing Parameters to a Statement16.2.5. Retrieving Rows from a Query16.2.6. Managing Transactions16.2.7. Getting Metadata16.2.8. Dynamically Processing a Result Set
16.3. Using Stored Programs with MySQLdb
16.3.1. Calling Simple Stored Programs16.3.2. Retrieving a Single Stored Program Result Set16.3.3. Retrieving Multiple Stored Program Result Sets16.3.4. Retrieving Dynamic Result Sets16.3.5. Obtaining Output Parameters
16.4. A Complete Example
16.5. Conclusion
17. Using MySQL Stored Programs with .NET
17.1. Review of ADO.NET Basics17.1.1. Installing the Connector/Net Driver and Configuring Your IDE17.1.2. Registering the Driver and Connecting to MySQL17.1.3. Issuing a Non-SELECT Statement17.1.4. Reusing a Statement Object17.1.5. Using Parameters17.1.6. Issuing a SELECT and Using a DataReader17.1.7. Getting DataReader Metadata17.1.8. DataSets17.1.9. Handling Errors17.1.10. Managing Transactions
17.2. Using Stored Programs in ADO.NET
17.2.1. Calling a Simple Stored Procedure17.2.2. Supplying Input Parameters17.2.3. Using a DataReader with a Stored Program17.2.4. Processing Multiple Result Sets in a DataReader17.2.5. Dynamically Processing Result Sets17.2.6. Using DataSets with Stored Programs17.2.7. Retrieving Output Parameters17.2.8. Calling Stored Functions
17.3. Using Stored Programs in ASP.NET
17.4. Conclusion
IV. Optimizing Stored Programs
18. Stored Program Security
18.1. Permissions Required for Stored Programs18.1.1. Granting Privileges to Create a Stored Program18.1.2. Granting Privileges to Modify a Stored Program18.1.3. Granting Privileges to Execute a Stored Program
18.2. Execution Mode Options for Stored Programs
18.2.1. The SQL SECURITY Clause18.2.2. Using Definer Rights to Implement Security Policies18.2.3. Stored Program or View?18.2.4. Handling Invoker Rights Errors
18.3. Stored Programs and Code Injection
18.3.1. Protecting Against SQL Injection with Stored Programs18.3.2. SQL Injection in Stored Programs
18.4. Conclusion
19. Tuning Stored Programs and Their SQL
19.1. Why SQL Tuning Is So Important19.1.1. An Instructive Example
19.2. How MySQL Processes SQL
19.2.1. Parsing SQL19.2.2. Caching19.2.2.1. Buffer pool and key cache19.2.2.2. Table cache19.2.2.3. Query cache19.2.2.4. Table statistics
19.3. SQL Tuning Statements and Practices
19.3.1. EXPLAIN Statement19.3.2. EXPLAIN and Stored Programs19.3.3. Details of the EXPLAIN Output19.3.4. Extended EXPLAIN19.3.5. Optimizer Hints19.3.6. Measuring SQL and Stored Program Execution19.3.7. The Slow Query Log
19.4. About the Upcoming Examples
19.5. Conclusion
20. Basic SQL Tuning
20.1. Tuning Table Access20.1.1. Index Lookup Versus Full Table Scan20.1.2. How MySQL Chooses Between Indexes20.1.3. Manually Choosing an Index20.1.4. Prefixed ("Partial") Indexes20.1.5. Concatenated Indexes20.1.5.1. Merging multiple indexes20.1.5.2. Covering indexes20.1.6. Comparing the Different Indexing Approaches20.1.7. Avoiding Accidental Table Scans20.1.7.1. Accidentally suppressing an index using a function20.1.7.2. Accidentally suppressing an index using a substring20.1.7.3. Creating concatenated indexes with a poor column order20.1.8. Optimizing Necessary Table Scans20.1.9. Using Merge or Partitioned Tables
20.2. Tuning Joins
20.2.1. How MySQL Joins Tables20.2.2. Joins Without Indexes20.2.3. Joins with Indexes20.2.4. Join Order20.2.5. A Simple Join Example
20.3. Conclusion
21. Advanced SQL Tuning
21.1. Tuning Subqueries21.1.1. Optimizing Subqueries21.1.2. Rewriting a Subquery as a Join21.1.3. Using Subqueries in Complex Joins
21.2. Tuning "Anti-Joins" Using Subqueries
21.2.1. Optimizing an Anti-Join
21.3. Tuning Subqueries in the FROM Clause
21.3.1. Using Views
21.4. Tuning ORDER and GROUP BY
21.4.1. Creating an Index to Avoid a Sort21.4.2. Reducing Sort Overhead by Increasing Sort Memory
21.5. Tuning DML (INSERT, UPDATE, DELETE)
21.5.1. Batching Inserts21.5.2. Optimizing DML by Reducing Commit Frequency21.5.3. Triggers and DML Performance
21.6. Conclusion
22. Optimizing Stored Program Code
22.1. Performance Characteristics of Stored Programs
22.2. How Fast Is the Stored Program Language?
22.3. Reducing Network Traffic with Stored Programs
22.4. Stored Programs as an Alternative to Expensive SQL
22.4.1. Avoid Self-Joins with Procedural Logic22.4.2. Optimize Correlated Updates
22.5. Optimizing Loops
22.5.1. Move Unnecessary Statements Out of a Loop22.5.2. Use LEAVE or CONTINUE to Avoid Needless Processing
22.6. IF and CASE Statements
22.6.1. Test for the Most Likely Conditions First22.6.2. Avoid Unnecessary Comparisons22.6.3. CASE Versus IF
22.7. Recursion
22.8. Cursors
22.9. Trigger Overhead
22.10. Conclusion
23. Best Practices in MySQL Stored Program Development
23.1. The Development Process
DEV-01: Set standards and guidelines before writing any code
DEV-02: Ask for help after 30 minutes on a problem
DEV-03: Walk through each other's code
DEV-04: Use independent testers for functional sign-off
DEV-05: Use source controlled files to maintain the "reference" copy of your stored routines
23.2. Coding Style and Conventions
STYL-01: Adopt a consistent, readable format that is easy to maintain
STYL-02: Adopt logical, consistent naming conventions for modules and data structures
STYL-03: Self-document using block and loop labels
STYL-04: Express complex expressions unambiguously using parentheses
STYL-05: Use vertical code alignment to emphasize vertical relationships
STYL-06: Comment tersely with value-added information
23.3. Variables
DAT-01: Use a consistent and meaningful variable naming style
DAT-02: Avoid overriding variable declarations within "inner" blocks
DAT-03: Replace complex expressions with functions
DAT-04: Remove unused variables and code
DAT-05: Don't assume that the result of an expression is TRUE or FALSE; it could be NULL
DAT-06: Employ "user" variables for global data sparingly
DAT-07: Create stored programs in strict mode to avoid invalid data assignments
23.4. Conditional Logic
IF-01: Use ELSEIF with mutually exclusive clauses
IF-02: Use IF...ELSEIF only to test a single, simple condition
IF-03: Make sure that a CASE statement is inclusive , or construct a handler to catch any unmatched cases
IF-04: Use CASE and IF consistently
23.5. Loop Processing
LOOP-01: Make sure the loop will terminate
LOOP-02: Make the termination conditions of a loop obvious
LOOP-03: Use a single LEAVE in simple loops
LOOP-04: Use a simple loop to avoid redundant code required by a WHILE or REPEAT UNTIL loop
23.6. Exception Handling
EXC-01: Handle exceptions that cannot be avoided but can be anticipated
EXC-02: Use named conditions to improve code readability
EXC-03: Be consistent in your use of SQLSTATE and MySQL error codes in exception handlers
EXC-04: Avoid global SQLEXCEPTION handlers until MySQL implements SIGNAL and SQLCODE features
23.7. SQL in Stored Programs
SQL-01: Start a transaction explicitly with the START TRANSACTION statement
SQL-02: Don't leave transactions "dangling "
SQL-03: Avoid use of savepoints —they can obscure program logic and reduce program efficiency
SQL-04: Use an appropriate locking strategy
SQL-05: Keep transactions small
SQL-06: Always reset the NOT FOUND variable after completing a cursor loop
SQL-07: Use SELECT FOR UPDATE when retrieving rows for later update
SQL-08: Avoid including SQL in functions that may be used in SQL
23.8. Dynamic SQL
DYN-01: Bind, do not concatenate, variable values into dynamic SQL strings
DYN-02: Carefully validate any parameter values that might be used to construct dynamic SQL
DYN-03: Consider the invoker rights method for stored code that executes dynamic SQL
23.9. Program Construction
PRG-01: Encapsulate business rules and formulas behind accurately named functions
PRG-02: Standardize module structure using function and procedure templates
PRG-03: Limit execution section sizes to a single page (50-60 lines) using modularization
PRG-04: Avoid side-effects in your programs
PRG-05: Avoid deep nesting of conditionals and loops
PRG-06: Limit functions to a single RETURN statement in the executable section
PRG-07: Use stored programs to implement code common to multiple triggers
23.10. Performance
PER-01: Concentrate on tuning SQL to improve stored program performance
PER-02: Carefully create the best set of indexes for your application
PER-03: Avoid accidental table scans
PER-04: Optimize necessary table scans
PER-05: Avoid using stored programs for computationally expensive routines
PER-06: Move loop invariant expressions outside of loops
PER-07: Optimize conditional structures
PER-08: Structure IF and CASE statements so more likely expressions appear earliest in the list
23.11. Conclusion
About the Authors
Colophon
SPECIAL OFFER: Upgrade this ebook with O’Reilly

Content preview from MySQL Stored Procedure Programming

Name

PER-04: Optimize necessary table scans

Synopsis

Using an index to retrieve rows from a table is worthwhile only when you are retrieving a relatively small subset of rows from the table. Over a certain proportion of the table (say 5-20%), it is more efficient to read every row from the table. However, it is still possible to optimize these "necessary" table scans. For instance:

You can move long, infrequently accessed columns to a secondary table.
You can create an index on all of the columns required for the query. MySQL can then scan the entire index to resolve the query. Since the index will normally be smaller than the table, it ought to be quicker to scan the index.

These techniques are discussed in detail in Chapter 21.

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

O’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

I 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

I’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

I'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

MySQL and JSON: A Practical Programming Guide

Publisher Resources

ISBN: 0596100892Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein

Name

Synopsis

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.

More than 5,000 organizations count on O’Reilly

Julian F.

Addison B.

Amir M.

Mark W.

You might also like

MySQL and JSON: A Practical Programming Guide

MySQL 8 Cookbook

MySQL Reference Manual

MySQL Cookbook

Publisher Resources