…? When a table gets dropped InnoDB deletes respective records from the dictionary -…NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (actor_id), -> KEY idx_actor_last_name (last_name) -> )ENGINE=InnoDB…is PRIMARY index and 0-19 is index idx_actor_last_name. It is not…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
…NOT NULL, `score` int(10) unsigned NOT NULL, PRIMARY KEY (`id`,`guest_language`,`guest_country`), KEY `guest_language` (`guest_language`,`guest_country`,`score`) ) ENGINE=InnoDB… three triggers: Not creating triggers because this is a dry run. CREATE TRIGGER `pt_osc_test_t_del` AFTER DELETE ON `…
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
…the testing phase and am evaluating InnoDb as alternative because deletes are awfully slow and my partitioned …is my current schema.. CREATE TABLE ObjectInstances_MRG_T ( `ObjInstance` VARCHAR(20) NOT NULL, `UTCTime` TIMESTAMP NOT NULL , `Version` SMALLINT(2) NOT NULL, `TType` SMALLINT(2) NOT NULL…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…ancestor CHAR(1) NOT NULL, descendant CHAR(1) NOT NULL, length INT NOT NULL DEFAULT 0, PRIMARY KEY (ancestor, descendant) ) ENGINE=InnoDB; Because …IS NULL; That deletes paths that terminate within the subtree (descendants of D), but not paths that begin within the subtree (paths whose ancestor is…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…is designed such that this is not the case. Updates and deletes of primary key values are instead restricted – that is not…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
…NOT NULL auto_increment, `c` tinyint(4) default NULL…is not directly used for lookup. Changing the key c to be BTREE we instantly get about 15000 of deletes per second which is…
Post: Recovering CREATE TABLE statement from .frm file
December 17, 2008 By 5 Comments
…NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +————-+—————————————————————————————————————————-+ So it is not…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…’ \G *************************** 1. row *************************** SQL: SELECT NULL as mview$pk, (customer_id)…is not useful, simply ignore this column. It is used to prevent wide innodb…is an optimization which is required to efficiently refresh MV which use those aggregate functions. Now I delete…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… true: mysql> create table a1(i int unsigned not null auto_increment primary key); Query OK, 0 rows affected… Merge Tables works differently from both MyISAM and Innodb tables and being similar to what ISAM tables … set (0.00 sec) As you can see delete is invisible in the merge table after we did …
Comment: Why MySQL could be slow with large tables ?
January 31, 2008 By 141 Comments
…NOT NULL DEFAULT ’0′, PRIMARY KEY (A,B,C,D), KEY (E), CONSTRAINT key_A FOREIGN KEY (A) REFERENCES ATable(A) ON DELETE…DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB…
