… Bar: CREATE TABLE Foo (A INT, B INT, PRIMARY KEY (A, B)); CREATE TABLE Bar (ID INT PRIMARY KEY, X INT, Y INT, FOREIGN KEY (X,Y) REFERENCES Foo(A… find all foreign keys by querying the INFORMATION_SCHEMA. The KEY_COLUMN_USAGE contains information about both primary keys and foreign keys. If the REFERENCED…
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
…), 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 (id), KEY (parent_id), CONSTRAINT child_ibfk_1 FOREIGN KEY…
Comment: Spreading .ibd files across multiple disks; the optimization that isn't
December 26, 2010 By 23 Comments
…(c1 int auto_increment, c2 int, c3 int, primary key(c1))engine=innodb; create table the_db.trx2(c4 int auto_increment, c1 int, key(c1), c5 int) engine=innodb; — c1 is FK to t1 create table the_db.log(log_id int auto_increment primary key, log1 int, log2 char(10), log3 int) engine=innodb; To move the_db…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
…` int(10) unsigned NOT NULL 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…
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 22, 2007 By 15 Comments
… int primary key, notid int) type=innodb; Query OK, 0 rows affected, 1 warning (0.07 sec) mysql> create table c (id int primary key, fk int , foreign key…> insert into p (id, notid) values (2,20) on duplicate key update notid=20; Query OK, 2 rows affected (0.01…
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_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child` ( `parent_id` int…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… unlike other benchmarks I decided to do Join not on primary key and have query to read data for both tables. If… on INT fields: CREATE TABLE `intjoin` ( `i` int(10) unsigned NOT NULL, `c` char(10) DEFAULT NULL, `j` int(10) unsigned NOT NULL, KEY `i` (`i`), KEY `j` (`j…
Comment: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
schema: create table t1 ( c1 int primary key, c2 int ); create table t2 ( id int primary key, t1_c1 int ); –given the query select c1 expr1, sum(c2…
Post: INSERT ON DUPLICATE KEY UPDATE and summary counters.
May 29, 2006 By 48 Comments
… tables. Here is example: create table ipstat(ip int unsigned not null primary key, hits int unsigned not null, last_hit timestamp); insert into ipstat values(inet_aton(’192.168.0.1′),1,now()) on duplicate key… hits=1 if it is already where (note ip is PRIMARY KEY) it would be just incremented and last visit timestamp updated…
Comment: Full table scan vs full index scan performance
November 28, 2012 By 15 Comments
… to the actual table pages. create table t ( c1 int primary key, c2 int, c3 int, …, c10 int key( c3, c10) ) — should use type: index because we…
