…of these Larger pages means slower index lookup as you need to scan half the page in average to find the key…to my tests it looks there is something special in how integer primary key…of queries…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find…TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) DEFAULT NULL, `status` enum(‘archived’,'active’) DEFAULT NULL, 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. If the query…to stress out join code while avoid sending data to the client Do not try to find any good meaning for query…
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…primary key. After identifying the Primary Key don’t forget to remove the innodb_table_monitor. C) Check the size on disk of…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…TABLE `idxitest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY…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: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…query/run_worker to contain the following: #!/bin/bash while [ 1 ] do ./worker >> /dev/null 2>&1 < /dev/null done; Where to find…of the last post. ICE does not have any indexes (not even primary keys…of the flight. Some data hand updated.’; — – Table structure for table `ontime_fact` — CREATE TABLE…
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… scalability (concurrent queries, autoincrement, concurrent inserts, …to implement online reorganize which locks and reorganizes small portions of table online. Q38: Seems that adaptive hash indexes are not used for primary key…
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, 0 rows affected (0.01 sec) mysql> create table a2 like a1; Query… value in table a2 was 1 it finds out what other subtable had value 2 and assigns the value 3 to the new…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…> CREATE TABLE `test2` ( -> `c` char(255) DEFAULT NULL, -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> ) ENGINE=MYISAM; Query OK, 0… bunch of similar statements to find exact number doing “binary search” Note even if you do not use MyISAM table but fetch data to the…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
…to create PRIMARY KEY and it will enforce unique values and will be used in the queries. The other case is simply having multiple keys…find them get rid of them. Note: Duplicate indexes apply to indexes of the same time. It may make sense to have indexes of different types to…
