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 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- taining [...]

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 [...]

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. This is both a session-level and a [...]

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 An OUTER JOIN doesn’t necessarily have to be executed as an [...]

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 [...]

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 [...]

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 step. 3. [...]

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 [...]

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 (*) 2. Find out [...]