…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: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 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: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) DEFAULT NULL, `status` enum(‘archived’,'active’) DEFAULT NULL, PRIMARY KEY… In this case, MySQL flipped from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really have to run…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 7 Comments
…Table: tables Create Table: CREATE TABLE `tables` ( `name` varchar(100) DEFAULT NULL, `spaceid` int(11) NOT NULL, PRIMARY KEY (`spaceid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…to InnoDB’s buffer pool. For this, you need to find a way to tell Linux to…table! If you have inserted in an ascending order of the PRIMARY KEY, then InnoDB should have placed the rows in…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…, to verify consistency. How can we do that? Quality Control Queries We need to check for orphaned rows in every parent-child relationship in your…)); CREATE TABLE Bar (ID INT PRIMARY KEY, X INT, Y INT, FOREIGN KEY (X,Y) REFERENCES Foo(A,B)); You can find orphaned rows in Bar…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
…in MySQL 5.1 Data Clustering This again applies to Innodb tables aspect of primary key selection – you often can gain a lot by selecting primary key…In general I stand the same point – be careful applying benchmarks you find in the Internet to…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…TABLE `test2` ( -> `c` char(255) DEFAULT NULL, -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> PRIMARY KEY…in the table and we need to somehow skip over it. To do it we would need to find…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…Table | Create Table | +——-+————————————————————————————————————————————————————————+ | am | CREATE TABLE `am` ( `i` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…tables. This time unlike other benchmarks I decided to do Join not on primary key and have query to read data for both tables…to stress out join code while avoid sending data to the client Do not try to find…
