Tutorial by Examples

This is a huge topic, but it is also the most important "performance" issue. The main lesson for a novice is to learn of "composite" indexes. Here's a quick example: INDEX(last_name, first_name) is excellent for these: WHERE last_name = '...' WHERE first_name = '...' AND ...

innodb_buffer_pool_size should be about 70% of available RAM.

x IN ( SELECT ... ) turn into a JOIN When possible, avoid OR. Do not 'hide' an indexed column in a function, such as WHERE DATE(x) = ...; reformulate as WHERE x = ... You can generally avoid WHERE LCASE(name1) = LCASE(name2) by having a suitable collation. Do no use OFFSET for "paginatio...

Here are some things that are not likely to help performance. They stem from out-of-date information and/or naivety. InnoDB has improved to the point where MyISAM is unlikely to be better. PARTITIONing rarely provides performance benefits; it can even hurt performance. Setting query_cache_size...

The most important thing for speeding up a query on any non-tiny table is to have a suitable index. WHERE a = 12 --> INDEX(a) WHERE a > 12 --> INDEX(a) WHERE a = 12 AND b > 78 --> INDEX(a,b) is more useful than INDEX(b,a) WHERE a > 12 AND b > 78 --> INDEX(a) or INDE...

A common mistake is to hide an indexed column inside a function call. For example, this can't be helped by an index: WHERE DATE(dt) = '2000-01-01' Instead, given INDEX(dt) then these may use the index: WHERE dt = '2000-01-01' -- if `dt` is datatype `DATE` This works for DATE, DATETIME, TIM...

In general OR kills optimization. WHERE a = 12 OR b = 78 cannot use INDEX(a,b), and may or may not use INDEX(a), INDEX(b) via "index merge". Index merge is better than nothing, but only barely. WHERE x = 3 OR x = 5 is turned into WHERE x IN (3, 5) which may use an index with x...

Subqueries come in several flavors, and they have different optimization potential. First, note that subqueries can be either "correlated" or "uncorrelated". Correlated means that they depend on some value from outside the subquery. This generally implies that the subquery mus...

A common problem that leads to an inefficient query goes something like this: SELECT ... FROM a JOIN b ON ... WHERE ... GROUP BY a.id First, the JOIN expands the number of rows; then the GROUP BY whittles it back down the the number of rows in a. There may not be any good c...