You searched for mysql force index key

Is Synchronous Replication right for your app?

May 14, 2013 By Jay Janssen 17 Comments

I talk with lot of people who are really interested in Percona XtraDB Cluster (PXC) and mostly they are interested in PXC as a high-availability solution. But, what they tend not to think too much about is if moving from async to synchronous replication is right for their application or not. Facts about Galera replication [...]

Filed Under: MySQL, Percona Software, XtraDB Cluster Tagged With: async, Galera replication, hotspots, Jay Janssen, Percona XtraDB Cluster, pxc, Synchronous Replication

More on MySQL transaction descriptors optimization

April 26, 2013 By Alexey Kopytov Leave a Comment

Since my first post on MySQL transaction descriptors optimization introduced in Percona Server 5.5.30-30.2 and a followup by Dimitri Kravchuk, we have received a large number of questions on why the benchmark results in both posts look rather different. We were curious as well, so we tried to answer that question by retrying benchmarks on [...]

Filed Under: Benchmarks, MySQL Tagged With: MySQL transaction descriptors, Optimization

MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

March 14, 2013 By Jaime Crespo 2 Comments

While preparing the webinar I will deliver this Friday, I ran into a quite interesting (although not very impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into a really nice exercise for checking the performance and inner workings of one [...]

Filed Under: Events and Announcements, Insight for Developers, MySQL Tagged With: Index Condition Pushdown, Jaime Crespo, MySQL 5.6.10

MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By Justin Swanhart 7 Comments

So far most of the benchmarks posted about MySQL 5.6 use the sysbench OLTP workload. I wanted to test a set of queries which, unlike sysbench, utilize joins. I also wanted an easily reproducible set of data which is more rich than the simple sysbench table. The Star Schema Benchmark (SSB) seems ideal for this. [...]

Filed Under: MySQL Tagged With: InnoDB plugin, Justin Swanhart, MySQL 5.5, MySQL 5.6, Star Schema Benchmark (SSB), sysbench

InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By Ernie Souhrada 7 Comments

This is part 2 in a 3 part series. In part 1, we took a quick look at some initial configuration of InnoDB full-text search and discovered a little bit of quirky behavior; here, we are going to run some queries and compare the result sets. Our hope is that the one of two things [...]

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: Ernie Souhrada, FTS, FullText Search, InnoDB, MyISAM, MySQL 5.6

Full table scan vs full index scan performance

November 23, 2012 By Stephane Combaudon 15 Comments

Earlier this week, Cédric blogged about how easy we can get confused between a covering index and a full index scan in the EXPLAIN output. While a covering index (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index) is according to the documentation the 2nd [...]

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: indexining, MySQL Index Scan

Concatenating MyISAM files

November 19, 2012 By Yves Trudeau 5 Comments

Recently, I found myself involved in the migration of a large read-only InnoDB database to MyISAM (eventually packed). The only issue was that for one of the table, we were talking of 5 TB of data, 23B rows. Not small… I calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take [...]

Filed Under: MySQL

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

DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

June 22, 2012 By Ovais Tariq 33 Comments

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL

Is Synchronous Replication right for your app?

More on MySQL transaction descriptors optimization

MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

Full table scan vs full index scan performance

Concatenating MyISAM files

Knowing what pt-online-schema-change will do

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

DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA