You are previewing Effective MySQL Optimizing SQL Statements.
O'Reilly logo
Effective MySQL Optimizing SQL Statements

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.

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