You searched for sql query to find record id of table

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

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

Analyzing Slow Query Table in MySQL 5.6

February 18, 2013 By Bill Karwin 1 Comment

Next week I’m teaching an online Percona Training class, called Analyzing SQL Queries with Percona Toolkit. This is a guided tour of best practices for pt-query-digest, the best tool for evaluating where your database response time is being spent. This month we saw the GA release of MySQL 5.6, and I wanted to check if any [...]

Filed Under: Events and Announcements, Insight for DBAs, Live Virtual Training, MySQL, Percona Toolkit, Training Tagged With: Amazon RDS, Bill Karwin, MySQL 5.6, Percona Toolkit, Slow Query Table, SQL, Tips, Training

Common MySQL traps webinar questions followup

January 17, 2013 By Stephane Combaudon 1 Comment

Thanks to all attendees of the webinar yesterday! If you missed it, you can watch the video recording. Here are some questions that remained unanswered due to time constraints. Q: Are there any technical considerations or best practice tips to have a replicated slave in the cloud, for example on Amazon AWS? Hardware resources are [...]

Filed Under: MySQL

How to find MySQL queries worth optimizing ?

September 11, 2012 By Peter Zaitsev 6 Comments

One question I often get is how one can find out queries which should be optimized. By looking at pt-query-digest report it is easy to find slow queries or queries which cause the large portion of the load on the system but how do we know whenever there is any possibility to make this query [...]

Filed Under: Insight for DBAs, MySQL

Full Text Search Webinar Questions Followup

August 24, 2012 By Bill Karwin 5 Comments

I presented a webinar this week to give an overview of several Full Text Search solutions and compare their performance. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording. During my webinar, a number of attendees asked some good questions. Here are their questions and my [...]

Filed Under: Events and Announcements, Insight for Developers, 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

Data compression in InnoDB for text and blob fields

May 30, 2012 By Michael Coburn 2 Comments

Have you wanted to compress only certain types of columns in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many heavily utilized TEXT fields with some read queries exceeding 500MB (!!), and stored in a 100GB table. In this [...]

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

How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By Miguel Angel Nieto 4 Comments

In my previous post I explained how it could be possible to recover, on some specific cases, a single table from a full backup in order to save time and make the recovery process more straightforward. Now the scenario is worse because we don’t have a backup or the backup restore process doesn’t work. How [...]

Filed Under: Insight for DBAs, MySQL

MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

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

InnoDB Full-text Search in MySQL 5.6 (part 1)

Analyzing Slow Query Table in MySQL 5.6

Common MySQL traps webinar questions followup

How to find MySQL queries worth optimizing ?

Full Text Search Webinar Questions Followup

Recovery after DROP & CREATE

Data compression in InnoDB for text and blob fields

How to recover deleted rows from an InnoDB Tablespace

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA