…TABLE `idxitest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY… scan until it finds a=100. This…key-checker key checker when it comes to redundant indexes. If you have query plans depending on Innodb ordering of data by primary key…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
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…row in set (0.01 sec) The cardinality of status index is woeful, but …microseconds (5.1 feature) and then find someway to parse and EXPLAIN all …
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
… CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of table sakila/actor …_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (actor_id), -> KEY idx_actor_last_name (last_name) ->…belongs to which database/table. Sometime it’s not possible to find the index_id. …
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… KEY. The queries being run against this table were almost exclusively INSERT … ON DUPLICATE KEY UPDATE… NULL, PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE=InnoDB; Now consider the following sequence of events. (root@…it, but then it checks the UNIQUE KEY and finds a violation, so instead it …
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… following: mysql> CREATE TABLE `test2` ( -> `c` char(255) DEFAULT NULL, -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> ) ENGINE=MYISAM… do bunch of similar statements to find exact number doing “binary search” Note even if you do not use MyISAM table but fetch…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… the Merge Table itself. Neither of these expectations really true: mysql> create table a1(i int unsigned not null auto_increment primary key); Query OK… you can see merge table behaves quite smart. Even though the maximum value in table a2 was 1 it finds out what other… | Table | Create Table | +——-+————————————————————————————————————————————————————————+ | am | CREATE TABLE `am` ( `i` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`i…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
…table: CREATE TABLE `test` ( `id` int(11) NOT NULL auto_increment, `c` tinyint(4) default NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=MEMORY and populated it with 1.000.000 rows ALL of…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…keys which should only require meta data change needs a table rebuild. In some real life production cases you might find tables…keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… scan keyblock from the start uncompressing keys to find matching key value. This means the following will …primary key is handled. I can’t see the index size difference between DEFAULT and 1 values for such key…. Let us try couple of benchmarks now. I’ve created simple table with 100.000 rows with…
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… Do not try to find any good meaning for query…of times for Innodb tables MyISAM Tables may suffer significantly if key compression is not disabled Joining on Shorter CHAR keys…
