…TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… this in MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0… Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), KEY… # test.test # ######################################################################## # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`), # Column types…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… are planning to write on multiple nodes. Have PRIMARY KEYS If you still have tables without PRIMARY KEYs, then its time to make one for them…, you can still hit issues like when automatic creation of primary keys for use during certification becomes non-deterministic. Although the previous… fixed on latest release (5.5.30-23.7.4), table without PK imposes an additional overhead, and because cluster performance is…
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… is important in your case rather than accepting general recommendations without giving much thought on which assumptions they are based. P…
Post: Using innodb_sys_tables and innodb_sys_indexes
June 15, 2012 By Leave a Comment
… this problem without additional information_schema tables we added in Percona Server. I was doing simple ALTER TABLE such as: “alter table sbtest add z int not null;” which I expected would create table with primary key only copy…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… TABLE performance with fast index creation and without it with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table… with expand_fast_index_creation=1 which builds table with primary key only when creating a table and when builds all indexes by sort…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…) Only changes from defaults was that both servers are compiled without the PERFORMANCE_SCHEMA, and paths are unique for basedir and… char(10), key(P_Name), key(P_MFGR), key(P_Category), key(P_Brand) ); DROP TABLE IF EXISTS supplier; CREATE TABLE supplier ( S_SuppKey int primary key, S…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… primary key order as well as get better feel for primary key and index pages and so using less space, it is just OPTIMIZE TABLE… That’s right ! Optimizing table straight away takes over 3 hours, while dropping indexes besides primary key, optimizing table and adding them back takes… is OK table is exposed without indexes for some time. Note though nothing stops you from using LOCK TABLES on Innodb table to ensure…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…disable foreign keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY (`parent… foreign keys disabled renaming parent table will have reference in child table updated to point to new table. However you can drop parent table without any…
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
… InnoDB course: CREATE TABLE parent ( id INT NOT NULL AUTO_INCREMENT, bogus_column char(32), PRIMARY KEY (id) ) ENGINE=InnoDB; CREATE TABLE child ( id INT NOT NULL AUTO_INCREMENT, parent_id INT NOT NULL, bogus_column char(32), PRIMARY KEY… rows in set (0.00 sec) The same example without the foreign key constraints does not block on the last statement.Â…
