June 20, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

… NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_A` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: ,

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

… NULL AUTO_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), KEY `col2` (`col2`), KEY `col2_2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET… 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 # To…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL

May 1, 2013 By 10 Comments

… you can, you should consider moving to InnoDB altogether. There are very few reasons… short term. Control Your Auto-Incrementing Columns PXC/Galera controls auto-incrementing values internally within the … multiple nodes. Have PRIMARY KEYS If you still have tables without PRIMARY KEYs, then its time…

Filed Under: Insight for DBAs, MySQL, Percona Software, XtraDB Cluster Tagged With: , , , ,

Post: AUTO_INCREMENT and MERGE TABLES

September 11, 2008 By Leave a Comment

… | am | CREATE TABLE `am` ( `i` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`i`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST… or for good you should remember auto_increment for Merge Tables works differently from both MyISAM and Innodb tables and being similar to…

Filed Under: Insight for DBAs Tagged With: ,

Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE

November 29, 2011 By 8 Comments

… InnoDB table with auto increment column will be like this: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY…` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 No…

Filed Under: MySQL

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… 4 million rows and one secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected… dump would actually make the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed, so it…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1

January 8, 2007 By 117 Comments

… size rows, auto increment primary key and couple of extra indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id` int(10) unsigned NOT NULL auto_increment, `name… query with access by primary key. InnoDB is faster than MyISAM by 6-9%. Falcon shows very bad scalabilty. READ_KEY_POINT Query: SELECT…

Filed Under: Benchmarks Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… varchar(100) DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name`,`details`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 We also …dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000): Incorrect column name ‘fts_doc_id’ …

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication

February 2, 2012 By 1 Comment

… AUTO_INCREMENT, PRIMARY KEY (`t1_c1`) ) ENGINE=InnoDB; CREATE TABLE `t2` ( `t2_c1` int(10) unsigned NOT NULL, PRIMARY KEY (`t2_c1`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t2_c1`) REFERENCES `t1` (`t1_c1`) ON UPDATE CASCADE ) ENGINE=InnoDB; [revin…

Filed Under: Insight for DBAs

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

… already) 6353 InnoDB: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY” of table “test”.”test”) InnoDB: Database…` char(255) DEFAULT NULL, -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> ) ENGINE=MYISAM; Query OK, 0 rows affected (0.03…

Filed Under: Insight for DBAs Tagged With:
« Older Posts