… ( id bigint(20) NOT NULL AUTO_INCREMENT, a varchar(100) DEFAULT NULL, b varchar(100) DEFAULT NULL, PRIMARY KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table… this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…NULL, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, KEY…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… about both primary keys and foreign keys. If the REFERENCED_* columns are non-null, it’s a foreign key. mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE…,2222), (333,444); mysql> INSERT INTO Bar (ID,X,Y) VALUES (21,333,444); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
…NULL default ’0′, `pathversion` tinyint(3) unsigned NOT NULL default ’0′, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY… worries – checking/repairing large MyISAM tables in case of MySQL/System crash is painful and great to be avoided. Long primary key – …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…NULL, `DayofMonth` tinyint(4) DEFAULT NULL, `DayOfWeek` tinyint(4) DEFAULT NULL, `FlightDate` date NOT NULL, `date_id` smallint(6) NOT NULL, PRIMARY KEY (`date_id`), KEY `FlightDate` (`FlightDate`), KEY…
Post: Connecting orphaned .ibd files
May 13, 2011 By 12 Comments
…) unsigned default NULL, `MIX_LEN` int(10) unsigned default NULL, `CLUSTER_NAME` varchar(255) default NULL, `SPACE` int(10) unsigned default NULL, PRIMARY KEY (`NAME`) ) ENGINE… NULL, PRIMARY KEY (`TABLE_ID`,`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Please note field SPACE. For table actor it is equal to 15: mysql… InnoDB: memory cache! To refrain from such error ibdconnect does check if the space_id is already used. It will refuse…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
…the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method …and foo.name is null; Query OK, 0 rows affected (0.00 sec) Now check the auto_increment counter…` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…) NOT NULL, `l_comment` varchar(44) NOT NULL, `l_shipmode` char(10) NOT NULL, PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `lineitem_fk2` (`l_suppkey`), KEY `lineitem…. Probably we need to check how really MRR works. What else worth to mention here is that MySQL is wrong choosing index…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
… NULL AUTO_INCREMENT, PRIMARY KEY (`t1_c1`) ) ENGINE=InnoDB; CREATE TABLE `t2` ( `t2_c1` int(10) unsigned NOT NULL, PRIMARY KEY…row in set (0.00 sec) Now let’s check the slave. [revin@forge rsandbox_5_5_17… ROW* based replication. When using the latter, MySQL will log separate statements for each row…
Post: Checking the subset sum set problem with set processing
May 16, 2011 By Leave a Comment
… sum check from SQL, which you can then distribute with Shard-Query: CREATE TABLE `the list` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `val` bigint(20) NOT NULL DEFAULT ’0′, PRIMARY KEY (`id`), KEY `id` (`id`) ) ENGINE=MyISAM; SELECT… cause our check to pass: mysql> insert into data (val) values (16); Query OK, 1 row affected (0.01 sec) mysql> SELECT val…
