Performance Tuning:
*
Performance Tuning
*
OR
Performance Tuning
suggest changeSyntax
- Don’t use DISTINCT and GROUP BY in the same SELECT.
- Don’t paginate via OFFSET, “remember where you left off”.
- WHERE (a,b) = (22,33) does not optimize at all.
- Explicitly say ALL or DISTINCT after UNION – it reminds you pick between the faster ALL or the slower DISTINCT.
- Don’t use SELECT *, especially if you have TEXT or BLOB columns that you don’t need. There is overhead in tmp tables and transmission.
- It is faster when the GROUP BY and ORDER BY can have exactly the same list.
- Don’t use FORCE INDEX; it may help today, but will probably hurt tomorrow.
Remarks
See also discussions about ORDER BY, LIKE, REGEXP, etc. Note: this needs editing with links and more Topics.
Cookbook on building optimal indexes.
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents
5
Insert
10
Delete
11
View
14
Comments
17
ALTER TABLE
18
Joins
19
UPDATE
21
JSON
22
MySQL Admin
23
TRIGGERS
27
SELECT
29
Group By
30
UNION
31
Drop Table
32
Data Types
33
Performance Tuning
34
Events
35
ENUM
37
Arithmetic
42
Log files
43
Partitioning
45
Backticks
46
mysqlimport
48
MySQL Unions
49
ORDER BY
50
MySQL client
53
Transaction
57
NULL
58
Replication
68
One to Many
72
Contributors