…(200) DEFAULT NULL, PRIMARY KEY (`id`), KEY `col2` (`col2`), KEY `col2_2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET=… # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY …
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`), CONSTRAINT `child_B_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`) ) ENGINE=InnoDB … relationships between columns/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ (woops!), single …
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…` is PRIMARY. If the primary key is not defined InnoDB will use a unique secondary index as the primary. If there is no any unique index InnoDB will…` varchar(255) DEFAULT NULL, PRIMARY KEY (`INDEX_ID`,`POS`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Fields names explain their content. Index id in our example is…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…) DEFAULT ” NOT NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I… SEQUENTIAL inserts into Primary Key, that this is quite suitable for InnoDB, and in it we have random inserts into SECONDARY KEYS (k), which…) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; That is, our PRIMARY KEY is not sequential anymore, which is bad for InnoDB, but…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… standalone Innodb instance Let’s break it down a bit. Our unit of locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a single Innodb node we… your hotspots were really bad in standalone Innodb, you might consider relaxing the fsync: set innodb_flush_log_at_trx_commit to…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same…innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb…oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT…
Post: Finally. How to verify if all MySQL records were recovered
April 25, 2013 By Leave a Comment
… are two ways to know how many records an InnoDB page stores. The index page has a header PAGE_N_RECS – this… know, records inside an InnoDB page are organized in an unidirectional list. The list is sorted by primary key and starts with the… in the page: ./constraints_parser -5f pages-actor/FIL_PAGE_INDEX/0-1599/00000000-00000003.page -V … Checking a page Infimum…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
Innodb primary key is special in many senses and I was always wondering how well MySQL is integrated with Innodb to take advantage of… every key also holds primary key value so primary key value can be read from index, making some queries index covered which previously was not: Notice “Using Index…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
…/pages=91% It says that PRIMARY key (which is the table by itself, as InnoDB is clustering data by primary key) takes 497839 pages ( 16KB… we insert into the primary key in order which makes things very predictable, but the inserts into the secondary key index are random – which leads to a lot of page splits. One helpful new feature to address this is in XtraDB/InnoDB…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys… if extra indexes are defined. The primary key itself does not get much larger whatever columns you place into it, as primary key BTREE contains…
