Effective MySQL optimizing SQL statements
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....
| Main Author: | |
|---|---|
| Format: | eBook |
| Language: | English |
| Published: |
New York
Oracle Press/McGraw-Hill
2011
|
| Series: | Oracle Press Ser
|
| Subjects: | |
| Online Access: | |
| Collection: | O'Reilly - Collection details see MPG.ReNa |
Table of Contents:
- 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
- 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
- Cover Page
- Title Page
- Copyright
- Contents
- Acknoledgements
- 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
- 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