Search Results for: explain

Percona Toolkit collection: pt-visual-explain

This is the first in a series of posts highlighting a few of the seldom-used but still handy Percona Toolkit tools. Have you ever had a problem understanding the EXPLAIN statement output? And are you the type of person who would rather use the command line than a GUI application? Then I would recommend that […]

When EXPLAIN estimates can go wrong!

I have been working with a few customer cases and one interesting case popped up. The customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the […]

Explaining Indexes with a Library Metaphor

My favorite metaphor for explaining indexes is comparing them to index cards in an old library. In an old library, you used to (or still do) have index cards at the front desk which have some brief description of the books in the library. They also used to be categorized alphabetically. (image taken from http://www.flickr.com/photos/reedinglessons/2239767394/) […]

EXPLAIN EXTENDED can tell you all kinds of interesting things

While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” which was added in MySQL 4.1 EXPLAIN EXTENDED can show you what the MySQL optimizer does to your query. You might not know this, but MySQL can dramatically change your query before it actually executes it. This process […]

Extended EXPLAIN

One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation as well as what other notes optimizer may wish to tell us. It is […]

Store UUID in an optimized way

A few years ago Peter Zaitsev, in a post titled “To UUID or not to UUID,” wrote: “There is timestamp based part in UUID which has similar properties to auto_increment and which could be used to have values generated at same point in time physically local in BTREE index.” For this post I’ve rearranged the timestamp part […]

Making HAProxy 1.5 replication lag aware in MySQL

HAProxy is frequently used as a software load balancer in the MySQL world. Peter Boros, in a past post, explained how to set it up with Percona XtraDB Cluster (PXC) so that it only sends queries to available nodes. The same approach can be used in a regular master-slaves setup to spread the read load across […]

InnoDB’s multi-versioning handling can be Achilles’ heel

I believe InnoDB storage engine architecture is great for a lot of online workloads, however, there are no silver bullets in technology and all design choices have their trade offs. In this blog post I’m going to talk about one important InnoDB limitation that you should consider. InnoDB is a multiversion concurrency control (MVCC) storage […]