You searched for how to give index in mysql - Page 3 of 13

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

Here’s a quick way to Foresee if Replication Slave is ever going to catch up and When!

August 29, 2012 By Aurimas Mikalauskas 11 Comments

If you ever had a replication slave that is severely behind, you probably noticed that it’s not catching up with a busy master at a steady pace. Instead, the “Seconds behind master” is going up and down so you can’t really tell whether the replica is catching up or not by looking at just few [...]

Filed Under: Insight for DBAs, MySQL Tagged With: Ideas, MySQL, Replication, Tips, Tools

Sell-an-Elephant-to-your-Boss-HOWTO

August 28, 2012 By Aurimas Mikalauskas 3 Comments

Spoiler alert: If your boss does not need an elephant, he is definitely NOT going to buy one from you. If he will, he will regret it and eventually you will too. I must appologize to the reader who was expecting to find an advice on selling useless goods to his boss. While I do [...]

Filed Under: Insight for DBAs, Insight for Developers Tagged With: Ideas, Performance

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

Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks

August 17, 2012 By Jay Janssen 17 Comments

Percona XtraDB Cluster (PXC) and the technology it uses (Galera) is an exciting alternative to traditional MySQL replication. For those who don’t know, it gives you: Fully Synchronous replication with a write latency increase equivalent to a ping RTT to the furthest node Automatic cluster synchronization, both incremental and full restores The ability to read [...]

Filed Under: MySQL, XtraDB Cluster

Recovery deleted ibdata1

August 10, 2012 By Aleksandr Kuzminsky 9 Comments

Recently I had a case when a customer deleted the InnoDB main table space – ibdata1 – and redo logs – ib_logfile*. MySQL keeps InnoDB files open all the time. The following recovery technique is based on this fact and it allowed to salvage the database. Actually, the files were deleted long time ago – [...]

Filed Under: Insight for DBAs, MySQL Tagged With: innodb recovery

Find unused indexes

June 30, 2012 By Miguel Angel Nieto Leave a Comment

I wrote one week ago about how to find duplicate indexes. This time we’ll learn how to find unused indexes to continue improving our schema and the overall performance. There are different possibilites and we’ll explore the two most common here. User Statistics from Percona Server and pt-index-usage. User Statistics User Statistics is an improvement [...]

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

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

Find and remove duplicate indexes

June 20, 2012 By Miguel Angel Nieto 9 Comments

Having duplicate keys in our schemas can hurt the performance of our database: They make the optimizer phase slower because MySQL needs to examine more query plans. The storage engine needs to maintain, calculate and update more index statistics DML and even read queries can be slower because MySQL needs update fetch more data to [...]

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

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

Here’s a quick way to Foresee if Replication Slave is ever going to catch up and When!

Sell-an-Elephant-to-your-Boss-HOWTO

Full Text Search Webinar Questions Followup

Recovery after DROP & CREATE

Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks

Recovery deleted ibdata1

Find unused indexes

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

Find and remove duplicate indexes

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA