This means primary keys and unique keys work as expected over the whole table,
and the MySQL query optimizer can optimize queries against partitioned tables more
intelligently than with merge tables.
Here are some important benefits of partitioned tables:
• You can specify that certain rows are stored together in one partition, which can
reduce the […]

The way MySQL implements merge tables has some important performance implica-
tions. As with any other MySQL feature, this makes them better suited for some uses
than others. Here are some aspects of merge tables you should keep in mind:
• A merge table requires more open file descriptors than a non-merge table con-
[…]

A character set is a mapping from binary encodings to a defined set of symbols; you
can think of it as how to represent a particular alphabet in bits. A collation is a set of
sorting rules for a character set. In MySQL 4.1 and later, every character-based value
can have a character set and a collation.* MySQL’s […]

Once you understand how the query cache works, it’s easy to tune. It has only a few
“moving parts”:
query_cache_type
Whether the query cache is enabled. Possible values are OFF, ON, or DEMAND, where
the latter means that only queries containing the SQL_CACHE modifier are eligible
for caching. […]

Reordering joins
Tables don’t always have to be joined in the order you specify in the query.
Determining the best join order is an important optimization; we explain it in
depth in “The join optimizer” on page 173.
Converting OUTER JOINs to INNER JOINs
[…]

Each MySQL connection, or thread, has a state that shows what it is doing at any
given time. There are several ways to view these states, but the easiest is to use the
SHOW FULL PROCESSLIST command (the states appear in the Command column). As a
query progresses through its lifecycle, its state changes many times, and there […]

Articles in this newsletter:
Highlights
- MySQL Enterprise Monitor with Enhanced Query Analyzer (Beta) is Available Now!
- Small to Mid-Size Business Solutions from MySQL
- For ISVs: Take the MySQL OEM Life Cycle Survey and Win a $150 Amazon.com Gift Certificate
- Case Study: thePlatform Migrates Media Publishing System to MySQL and Achieves 23x Performance Gain with the MySQL […]

Follow along with the illustration to see what happens when you send MySQL a
query:
1. The client sends the SQL statement to the server.
2. The server checks the query cache. If there’s a hit, it returns the stored result
from the cache; otherwise, it passes the SQL statement to the next […]

Many high-performance web sites use join decomposition. You can decompose a join
by running multiple single-table queries instead of a multitable join, and then per-
forming the join in the application. For example, instead of this single query:
mysql> SELECT * FROM tag
         ->    JOIN tag_post ON tag_post.tag_id=tag.id
         ->    JOIN post ON tag_post.post_id=post.id
         -> WHERE tag.tag='mysql';
You might run […]

We’ve found it useful to analyze a poorly performing query in two steps:
1. Find out whether your application is retrieving more data than you need. That
usually means it’s accessing too many rows, but it might also be accessing too
many columns. Fetching more rows than needed, Fetching all columns […]

We’re not trying to write an exhaustive list; our goal is just to
present some key factors that are relevant to schema design.
The MyISAM Storage Engine
Table locks
MyISAM tables have table-level locks. Be careful this doesn’t become a
bottleneck.
No automated data recovery
If the MySQL server […]

Normalized updates are usually faster than denormalized updates.
• When the data is well normalized, there’s little or no duplicated data, so there’s
less data to change.
• Normalized tables are usually smaller, so they fit better in memory and perform
better.
• The lack of redundant data means there’s less need for DISTINCT […]

We’ve covered the most important data type considerations (some with serious and
others with more minor performance implications), but we haven’t yet told you about
the evils of autogenerated schemas.
Badly written schema migration programs and programs that autogenerate schemas
can cause severe performance problems. Some programs use large VARCHAR fields for
everything, or use different data types for columns […]

MySQL supports a large variety of data types, and choosing the correct type to store
your data is crucial to getting good performance. The following simple guidelines can
help you make better choices, no matter what type of data you are storing:
Smaller is usually better.
In general, try to use the smallest data type […]

InnoDB is generally pretty robust. It is built to be reliable, and it has a lot of built-in
sanity checks to prevent, find, and fix corrupted data—much more so than some
other storage engines. However, it can’t protect itself against everything.
At a minimum, InnoDB relies on unbuffered I/O calls and fsync( ) calls not return-
ing until the […]

• Backups come first. You can’t recover unless you’ve first backed up, so your
attention naturally focuses on backups when building a system. It’s important to
counter this tendency by planning for recovery first. In fact, you shouldn’t build
your backup systems until you figure out your recovery requirements.
• Backups are routine. […]

In other words, benchmarking answers the question
“How well does this perform?” and profiling answers the question “Why does it per-
form the way it does?”
[…]

To get the most from MySQL, you need to understand its design so that you can
work with it, not against it. MySQL is flexible in many ways.
MySQL’s most unusual and important feature is its storage-engine
architecture, whose design separates […]

SQL style guide

Style guide background

This style guide describes how you should name and design the database for you
application module.
The base idea behind all this is to improve the usability, performance, maintainance
and understandability of you database part and application.
This guide tries to be database independent. So using it should make your scheme more
portable to different dbms.

Naming […]

You can check whether the query cache is present in your MySQL server using the following statement:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| have_query_cache | YES   |
+——————+——-+
You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.
The RESET […]