I presented a webinar this week to give an overview of Tools and Techniques for Index Design. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording. I’d like to invite folks who are interested in tools for query optimization to attend the new Percona [...]
When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
Inspiration for this post is courtesy of a friend and former colleague of mine, Greg Youngblood, who pinged me last week with an interesting MySQL puzzle. He was running Percona Server 5.5.21 with a table structure that looks something like this:
1 2 3 4 5 6 7 8 9 | CREATE TABLE foo ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, uid INT UNSIGNED NOT NULL, update_time DATETIME NOT NULL, .... INDEX `uid` (uid, update_time), INDEX `bar` (some_other_columns) .... ) ENGINE=InnoDB; |
When he ran this query:
1 | SELECT MIN(update_time) FROM foo WHERE update_time IS NOT NULL AND update_time <> '0000-00-00 00:00:00'; |
The result came back as 2012-06-22 10:28:16. [...]
Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
I got the message in the morning today about the bug being fixed in MySQL 5.6.6…. which I reported in Early 2006 (while still being with MySQL) and running MySQL 4.1 I honestly thought this issue was fixed long ago as it was indeed pretty annoying. I must say I’m very impressed with Oracle team [...]
MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had lots of questions. I did not have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish (25-30) indexed varchars with [...]
Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
This post is a step-by-step guide to set up Percona XtraDB Cluster (PXC) in a virtualized test sandbox. I used Amazon EC2 micro instances, but the content here is applicable for any kind of virtualization technology (for example VirtualBox). The goal is to give step by step instructions, so the setup process is understandable and [...]
A case for MariaDB’s Hash Joins
May 31, 2012 By Leave a Comment
MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks [...]
Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5 [...]
Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
This is the second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take a look at [...]
Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available [...]
How to convert MySQL’s SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
SHOW PROFILES shows how much time MySQL spends in various phases of query execution, but it isn’t a full-featured profile. By that, I mean that it doesn’t show similar phases aggregated together, doesn’t sort them by worst-first, and doesn’t show the relative amount of time consumed. I’ll profile the “nicer_but_slower_film_list” included with the Sakila sample [...]
