MySQL Manual- 1 General Information
- 2 MySQL Installation
- 3 Tutorial Introduction
- 4 Database Administration
- 5 MySQL Optimisation
- 6 MySQL Language Reference
- 7 MySQL Table Types
- 8 National Character Sets and Unicode in MySQL 4.1
- 9 MySQL APIs
- 10 Error Handling in MySQL
- 11 Spatial Extensions in MySQL
- 12 Extending MySQL
- A Problems and Common Errors
- B Contributed Programs
- C Credits
- D MySQL Change History
- E Porting to Other Systems
- F Environment Variables
- G MySQL Regular Expressions
- H GNU General Public License
- I GNU Lesser General Public License
- SQL command, type and function index
- Concept Index
5 MySQL Optimisation
Optimisation is a complicated task because it ultimately requires understanding of the whole system. While it may be possible to do some local optimisations with small knowledge of your system or application, the more optimal you want your system to become the more you will have to know about it.
This chapter will try to explain and give some examples of different ways to optimise MySQL. Remember, however, that there are always some (increasingly harder) additional ways to make the system even faster.
Subsections
- 5.1 Optimisation Overview
- 5.1.1 MySQL Design Limitations/Tradeoffs
- 5.1.2 Portability
- 5.1.3 What Have We Used MySQL For?
- 5.1.4 The MySQL Benchmark Suite
- 5.1.5 Using Your Own Benchmarks
- 5.2 Optimising
SELECTs and Other Queries - 5.2.1
EXPLAINSyntax (Get Information About aSELECT) - 5.2.2 Estimating Query Performance
- 5.2.3 Speed of
SELECTQueries - 5.2.4 How MySQL Optimises
WHEREClauses - 5.2.5 How MySQL Optimises
IS NULL - 5.2.6 How MySQL Optimises
DISTINCT - 5.2.7 How MySQL Optimises
LEFT JOINandRIGHT JOIN - 5.2.8 How MySQL Optimises
ORDER BY - 5.2.9 How MySQL Optimises
LIMIT - 5.2.10 Speed of
INSERTQueries - 5.2.11 Speed of
UPDATEQueries - 5.2.12 Speed of
DELETEQueries - 5.2.13 Other Optimisation Tips
- 5.3 Locking Issues
- 5.4 Optimising Database Structure
- 5.4.1 Design Choices
- 5.4.2 Get Your Data as Small as Possible
- 5.4.3 How MySQL Uses Indexes
- 5.4.4 Column Indexes
- 5.4.5 Multiple-Column Indexes
- 5.4.6 Why So Many Open tables?
- 5.4.7 How MySQL Opens and Closes Tables
- 5.4.8 Drawbacks to Creating Large Numbers of Tables in the Same Database
- 5.5 Optimising the MySQL Server
- 5.5.1 System/Compile Time and Startup Parameter Tuning
- 5.5.2 Tuning Server Parameters
- 5.5.3 How Compiling and Linking Affects the Speed of MySQL
- 5.5.4 How MySQL Uses Memory
- 5.5.5 How MySQL uses DNS
- 5.5.6
SETSyntax - 5.6 Disk Issues
User Comments
| Posted by Christoph Tapper on Monday October 7 2002, @3:14pm | [Delete] [Edit] |
Slow SQLs may be a result of unintended locks.
I had trouble with an SELECT that joined 4 tables but should return only 1 row (LIMIT 1). After returning the result the connection still was heavy busy (a bug?). The CPU usage of mysqld was about 97% and SHOW PROCESSLIST showed that the connection was in a state "Copying to tmp table" and thus LOCKING all tables participating in the query.
The solution was to split the SQL into multiple CREATE TEMPORARY TABLE SQLs and then joining these. That reduced the execution speed from about 300 seconds to something below 1 second :)
