…TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE…to parent via parent_id, however in child_B it is explicit. This has an impact on how…
Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 1
May 21, 2013 By 8 Comments
… manual describes very nicely how to switch to GTID-based replication, …the same time? To find an answer to these questions, let… something in the middle. What if we try to reconfigure …table on the master: mysql> create table test.t (id int not null auto_increment primary key); Executing SHOW TABLES…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…query optimizer and see how easy it is? ;-) … in a suboptimal form. Q: Doesn’t the primary key…to be consecutive. Then you could choose a random value, look up the row with “=” instead of “>” and be guaranteed to find…temporary table, and making lookups to the `kind_types` table by primary key, the…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…in the primary index we can find all fields. It must exist for any InnoDB table. If explicitely defined its `NAME` is PRIMARY. If the primary key…to know what fields form the primary key. The matter is regardless at what position primary key fields are defined in CREATE TABLE…
Post: Repair MySQL 5.6 GTID replication by injecting empty transactions
March 26, 2013 By 1 Comment
In a previous post I explained how to repair MySQL…in slave server is not working because of an error: Last_SQL_Error: Error ‘Duplicate entry ’4′ for key ‘PRIMARY… we need to find a way to ignore that transaction. The way to do …. pt-table-checksum can help you here, which is found in Percona Toolkit…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…how one can find out queries which should be optimized. By looking at pt-query-digest report it is easy to find…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
…you need to scan half the page in average to find the key value So how you … Also according to my tests it looks there is something special in how integer primary key is …to keep it simple: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL , `c` varchar(20) NOT NULL default ”, KEY `c` (`c`), KEY…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…: mysql> show create table t; [...] PRIMARY KEY (`i`), KEY `key_name` (`name`,`i`) In this example the index ‘key_name’ includes the primary key so that last … PRIMARY KEY is indeed UNIQUE. How can I find all those keys? There is a tool in Percona Toolkit that can help you to find all those keys in…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
…to identify which of those two indexes is the Primary Key. This is our next step. 2- Identifying the Primary Key There are different methods to find…to explain three of them: A) INNODB_SYS_INDEXES Percona Server has some extra tables in INFORMATION_SCHEMA that can help us to find…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…how to find duplicate indexes. This time we’ll learn how to find unused indexes to…
