…: 0 And now on to a more interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0… Used Query OK, 3 rows affected (0.02 sec) mysql> ALTER TABLE `child` ADD PRIMARY KEY (`id`,`parent_id`); — 0 Tickets Used Query OK, 0…: 0 So, how can we put this into practice, since this information isn’t available to most users? INSERT w/PRIMARY KEY defined: Number of…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…ALTER TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table rebuild to…to do it and we can…TABLE `huge_table` ( `id` int(6) NOT NULL auto_increment, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB To remove auto_increment, we (1) create table…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…table containing 4 million rows and one secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY…ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing foreign keys…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… right ? mysql> alter table idxitest drop key a,add key(a,b); Query …key value. So when you have index (a) and id is a primary key the real index is (a,id) when we extend index to…to redundant indexes. If you have query plans depending on Innodb ordering of data by primary key inside indexes they can…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…can’t use or see. I’ve also seen others try to make InnoDB use a composite UNIQUE key on a table as a primary key…ALTER TABLE or OPTIMIZE TABLE > now locks small table for few seconds rather than giant 100GB table > for few hours so can…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…can actually disable foreign keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY…key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…ALTER TABLE — a typical reason to…can we improve here? Especially assuming that there are indexes other than the primary key, we can shrink the primary key‘s width: create table…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…+———-+ Seems like a very successful day! 
When we…to say about this? *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: PRIMARY key: PRIMARY key…ALTER TABLE items_ordered ADD itm_order_date DATE NOT NULL, ADD… You can find …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…table `test/child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE Trying to add in child table…table `test/parent`, in index `PRIMARY`, the closest match we can…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
… varchar(3) NOT NULL DEFAULT ”, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), KEY `country_id` (`country_id`), KEY `state_id` (`state_id`), ) …to read row data from the table. So – we can extend index `state_id_idx` (`state_id`) by two columns: ALTER TABLE userinfo DROP KEY state_id, ADD KEY…
