Effective MySQL Optimizing SQL Statements

Effective MySQL Optimizing SQL Statements

by Ronald Bradford
Released October 2011
Publisher(s): Oracle Press
ISBN: 9780071782807

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The Essential Guide to SQL Statement Optimization

Written by Oracle ACE Director and MySQL expert Ronald Bradford, Effective MySQL: Optimizing SQL Statements is filled with detailed explanations and practical examples that can be applied immediately to improve database and application performances. Featuring a step-by-step approach to SQL optimization, this Oracle Press book helps you to analyze and tune problematic SQL statements.

  • Identify the essential analysis commands for gathering and diagnosing issues
  • Learn how different index theories are applied and represented in MySQL
  • Plan and execute informed SQL optimizations
  • Create MySQL indexes to improve query performance
  • Master the MySQL query execution plan
  • Identify key configuration variables that impact SQL execution and performance
  • Apply the SQL optimization lifecycle to capture, identify, confirm, analyze, and optimize SQL statements and verify the results
  • Improve index utilization with covering indexes and partial indexes
  • Learn hidden performance tips for improving index efficiency and simplifying SQL statements

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright
  4. Contents
  5. Acknoledgements
  6. Introduction
  7. 1 The Five Minute DBA
    1. Identifying Performance Problems
      1. Finding a Slow SQL Statement
      2. Confirming the Slow Query
    2. Optimizing Your Query
      1. What You Should Not Do
      2. Confirm Your Optimization
      3. The Correct Approach
      4. An Alternative Solution
    3. Conclusion
  8. 2 The Essential Analysis Commands
    1. EXPLAIN
      1. EXPLAIN PARTITIONS
      2. EXPLAIN EXTENDED
    2. SHOW CREATE TABLE
    3. SHOW INDEXES
    4. SHOW TABLE STATUS
    5. SHOW STATUS
    6. SHOW VARIABLES
    7. INFORMATION_SCHEMA
    8. Conclusion
  9. 3 Understanding MySQL Indexes
    1. Example Tables
    2. MySQL Index Usages
      1. Data Integrity
      2. Optimizing Data Access
      3. Table Joins
      4. Sorting Results
      5. Aggregation
    3. About Storage Engines
    4. Index Terminology
    5. MySQL Index Types
      1. Index Data Structure Theory
      2. MySQL Implementation
    6. MySQL Partitioning
    7. Conclusion
  10. 4 Creating MySQL Indexes
    1. Example Tables
    2. Existing Indexes
    3. Single Column Indexes
      1. Syntax
      2. Restricting Rows with an Index
      3. Joining Tables with an Index
      4. Understanding Index Cardinality
      5. Using Indexes for Pattern Matching
      6. Selecting a Unique Row
      7. Ordering Results
    4. Multi Column Indexes
      1. Determining Which Index to Use
      2. Multi Column Index Syntax
      3. Providing a Better Index
      4. Many Column Indexes
      5. Combining WHERE and ORDER BY
      6. MySQL Optimizer Features
      7. Query Hints
      8. Complicated Queries
    5. The Impact of Adding Indexes
      1. DML Impact
      2. DDL Impact
      3. Disk Space Impact
    6. MySQL Limitations
      1. Cost Based Optimizer
      2. QEP Pinning
      3. Index Statistics
      4. Function Based Indexes
      5. Multiple Indexes per Table
    7. Conclusion
  11. 5 Creating Better MySQL Indexes
    1. Better Indexes
      1. Covering Index
      2. Storage Engine Implications
      3. Partial Index
    2. Conclusion
  12. 6 MySQL Configuration Options
    1. Memory Related Variables
      1. key_buffer_size
      2. Named Key Buffers
      3. innodb_buffer_pool_size
      4. innodb_additional_mem_pool_size
      5. query_cache_size
      6. max_heap_table_size
      7. tmp_table_size
      8. join_buffer_size
      9. sort_buffer_size
      10. read_buffer_size
      11. read_rnd_buffer_size
    2. Instrumentation Related Variables
      1. slow_query_log
      2. slow_query_log_file
      3. general_log
      4. general_log_file
      5. long_query_time
      6. log_output
      7. Profiling
    3. Other Optimization Variables
      1. optimizer_switch
      2. default_storage_engine
      3. max_allowed_packet
      4. sql_mode
      5. innodb_strict_mode
    4. Other Variables
    5. Conclusion
  13. 7 The SQL Lifecycle
    1. Capture Statements
      1. General Query Log
      2. Slow Query Log
      3. Binary Log
      4. Processlist
    2. Engine Status
      1. MySQL Connectors
      2. Application Code
      3. INFORMATION_SCHEMA
      4. PERFORMANCE_SCHEMA
      5. SQL Statement Statistics Plugin
      6. MySQL Proxy
      7. TCP/IP
    3. Identify Problematic Statements
      1. Slow Query Log Analysis
      2. TCP/IP Analysis
    4. Confirm Statement Operation
      1. Environment
      2. Timing
    5. Analyze Statements
    6. Optimize Statements
    7. Verify the Results
    8. Conclusion
  14. 8 Hidden Performance Tips
    1. Index Management Optimizations
      1. Combining Your DDL
      2. Removing Duplicate Indexes
      3. Removing Unused Indexes
      4. Monitoring Ineffective Indexes
    2. Index Column Improvements
      1. Data Types
      2. Column Types
    3. Other SQL Optimizations
      1. Eliminating SQL Statements
      2. Simplifying SQL Statements
      3. Using MySQL Replication
    4. Conclusion
  15. 9 Explaining the MySQL EXPLAIN
    1. Syntax
    2. Explain Columns
      1. key
      2. rows
      3. possible_keys
      4. key_len
      5. table
      6. select_type
      7. partitions
      8. Extra
      9. id
      10. ref
      11. filtered
      12. type
    3. Interpreting EXPLAIN Output
    4. Conclusion
  16. Index

Product information

  • Title: Effective MySQL Optimizing SQL Statements
  • Author(s): Ronald Bradford
  • Release date: October 2011
  • Publisher(s): Oracle Press
  • ISBN: 9780071782807