Sign in  |  Register
Advanced search
Publication Cover

CITATION

Bradford, Ronald. Effective MySQL Optimizing SQL Statements. US: McGraw-Hill Osborne Media, 2011.

Effective MySQL Optimizing SQL Statements

Authors: Ronald Bradford

Published:  September 2011

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