You searched for mysql explain possible key null

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 1)

February 26, 2013 By Ernie Souhrada 1 Comment

I’ve never been a very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by the potential disadvantages and the strengths of InnoDB. However, up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS). And I’ve encountered many [...]

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

The Optimization That (Often) Isn’t: Index Merge Intersection

December 14, 2012 By Ernie Souhrada 6 Comments

Prior to version 5.0, MySQL could only use one index per table in a given query without any exceptions; folks that didn’t understand this limitation would often have tables with lots of single-column indexes on columns which commonly appeared in their WHERE clauses, and they’d wonder why the EXPLAIN plan for a given SELECT would [...]

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

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

Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6

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

Filed Under: Insight for DBAs, MySQL

Recovery after DROP & CREATE

August 22, 2012 By Aleksandr Kuzminsky 1 Comment

In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:

DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `actor`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `actor` (

`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`first_name` varchar(45) NOT NULL,

`last_name` varchar(45) NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`actor_id`),

KEY `idx_actor_last_name` (`last_name`)

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

If there were no subsequent CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of [...]

Filed Under: MySQL Tagged With: drop table, InnoDB, Recovery

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

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

Avoiding auto-increment holes on InnoDB with INSERT IGNORE

November 29, 2011 By Miguel Angel Nieto 8 Comments

Are you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE query creates gaps for every ignored insert, but this is undocumented behaviour. This documentation bug is already submitted. Firstly, we will start with a simple question. Why do we have [...]

Filed Under: MySQL

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 1)

The Optimization That (Often) Isn’t: Index Merge Intersection

Full table scan vs full index scan performance

Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6

Recovery after DROP & CREATE

A case for MariaDB’s Hash Joins

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

Avoiding auto-increment holes on InnoDB with INSERT IGNORE

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA