…clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`), # Column types: # `col2` int(11) default null # `id` int(11) not null auto_increment…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…the furthest node Automatic cluster synchronization, both incremental and full restores The…mysql> create table autoinc ( i int unsigned not null auto_increment primary key, j varchar(32) ); Query OK, 0 rows affected (0.02 sec) node2 mysql…no 823 n bits 72 index `PRIMARY` of table `test`.`autoinc` trx…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… one secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> INSERT… no PRIMARY KEY defined, because in this case InnoDB picks such an index as the clustered one. References: Peter’s post MySQL bug #57583 MySQL bug…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… with InnoDB? Data clustering. InnoDB’s primary keys define the physical order…define the primary key as (day, ad). Don’t use an auto-increment primary key, and… there are indexes other than the primary key, we can shrink the primary key‘s …using MySQL 5.1′s row-based replication, or in MySQL 5….
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
…auto_increment primary keys, basically advertising to use UUID instead of primary keys…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…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…MySQL optimizer restrictions in how well it can deal with primary key appended to the index …which cases should auto-increment be used as primary key? A: Auto-increment is a good default primary key. You should…clustering in the different way or if you have some other natural candidate for primary key…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…uses a clustered index based on (in order) the primary key you specify (an auto_increment), a unique key, or …t create either a unique key or a column with an auto_increment primary key in a table using… database background. > Things are different with MySQL. Good design will help dictate how …
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
…auto increment primary key and couple of extra indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id` int(10) unsigned NOT NULL auto_increment…KB 4GB of RAM MySQL version We used MySQL 5.1.16-beta …clustering by primary key. InnoDB does not need an extra lookup to access to data. READ_PK_RANGE_INDEX…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…PRIMARY KEY (`date_id`), KEY `FlightDate` (`FlightDate`), KEY `Year` (`Year`,`Quarter`,`Month`,`DayOfWeek`), KEY `Quarter` (`Quarter`,`Month`,`DayOfWeek`), KEY `Month` (`Month`,`DayOfWeek`), KEY…Index_length: 539279360 Data_free: 4194304 Auto_increment…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
…primary key – why did not I use auto_increment id in this case ? I could but this would kill clustering…indexes are defined. The primary key itself does not get much larger whatever columns you place into it, as primary key…
