You searched for mysql group by two columns

Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available

April 17, 2013 By Hrvoje Matijakovic 2 Comments

Percona is glad to announce the release of Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL on April 17, 2013. Binaries are available from the downloads area or from our software repositories. New Features: Percona XtraDB Cluster has implemented initial implementation of weighted quorum. Weight for node can be assigned via pc.weight option in the wsrep_provider_options variable. [...]

Filed Under: MySQL, XtraDB Cluster Tagged With: libjemalloc library, pc.weight, Percona XtraDB Cluster

Sphinx search performance optimization: attribute-based filters

January 15, 2013 By Aurimas Mikalauskas 9 Comments

One of the most common causes of a poor Sphinx search performance I find our customers face is misuse of search filters. In this article I will cover how Sphinx attributes (which are normally used for filtering) work, when they are a good idea to use and what to do when they are not, but [...]

Filed Under: Insight for Developers, MySQL Tagged With: FullText Search, Sphinx, Tips

Realtime stats to pay attention to in Percona XtraDB Cluster and Galera

November 26, 2012 By Jay Janssen 4 Comments

I learn more and more about Galera every day. As I learn more, I try to keep my myq_gadgets toolkit up to date with what I consider is important to keep any eye on on a PXC node. In that spirit, I just today pushed some changes to the ‘wsrep’ report, and I thought I’d go over [...]

Filed Under: MySQL, XtraDB Cluster

Knowing what pt-online-schema-change will do

November 1, 2012 By Daniel Nichter 1 Comment

pt-online-schema-change is simple to use, but internally it is complex. Baron’s webinar about pt-online-schema-change hinted at several of the tool’s complexities. Consequently, users often want to know before making changes what pt-online-schema-change will do when it runs. The tool has two options to help answer this question: –dry-run and –print. When ran with –dry-run and –print, pt-online-schema-change changes nothing [...]

Filed Under: Insight for DBAs, Percona Software Tagged With: pt-online-schema-change

When is MIN(DATE) != MIN(DATE) ?

September 4, 2012 By Ernie Souhrada 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:

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;

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:

SELECT MIN(update_time) FROM foo WHERE update_time IS NOT NULL AND update_time <> '0000-00-00 00:00:00';

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

Filed Under: Insight for DBAs, Insight for Developers, MySQL

MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By Peter Zaitsev 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 [...]

Filed Under: Insight for DBAs, MySQL

A case for MariaDB’s Hash Joins

May 31, 2012 By Ovais Tariq 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 [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: batched key access, block nested loop, hash join, JOIN Performance, MariaDB, Performance

Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By Ovais Tariq 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 [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: batched key access, BKA, hash join, io, JOIN Performance, join_buffer, MariaDB, mrr_buffer, multi range read, MySQL Performance, nested loop join, Optimizer, random read, read_rnd_buffer, sequential read

Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By Ovais Tariq 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 [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: InnoDB, MariaDB, mrr_buffer, multi range read, MySQL, MySQL Index Scan, MySQL Indexes, Optimization, Optimizer, query, random read, range lookup, read_rnd_buffer, secondary key lookup, sequential read

Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By Ovais Tariq 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 [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: Benchmarks, InnoDB, IO Performance, MariaDB, MySQL, MySQL Indexes, MySQL Performance, Optimizer

Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available

Sphinx search performance optimization: attribute-based filters

Realtime stats to pay attention to in Percona XtraDB Cluster and Galera

Knowing what pt-online-schema-change will do

When is MIN(DATE) != MIN(DATE) ?

MySQL Indexing Best Practices: Webinar Questions Followup

A case for MariaDB’s Hash Joins

Join Optimizations in MySQL 5.6 and MariaDB 5.5

Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA