…=latin1 1 row in set (0.00 sec) Such automatically table generated names are not really helpful for multiple column indexes – you can…: mysql> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key(i); Query OK…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…mysql> alter table parent rename parentx; Query OK, 0 rows affected (0.05 sec) mysql> drop table parentx; Query OK, 0 rows affected (0.61 sec) mysql…table the same way as when there are non existing rows – if you’re trying to change “parent_id” column in child table…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
…column or something similar. It also produces a lot better fill factor. The problem is…. OPTIMIZE TABLE for Innodb tables… | +——–+———-+———-+——————————————————————-+ 2 rows in set (4 min 5.52 sec) mysql> alter table a add key(c); Query OK,…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
…mysql> drop table salaries; — Ooops! Query OK, 0 rows affected (0.49 sec) mysql> alter table employees add column age smallint unsigned not null default 0; Query…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…column (a) to column (a,b) right which will make it faster and should not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK,…. Optimizer behavior may be different in different MySQL versions. These tests were …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… method is used: mysql> SET profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD COLUMN v VARCHAR(1); Query OK, 4194304 rows… in the table makes any difference: mysql> SET expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD KEY (c), ADD…
Post: Converting Character Sets
March 17, 2009 By 16 Comments
…mysql> CREATE TABLE `t1` ( ->Â Â `c1` text NOT NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE…ALTER statement against the table in Approach #1: ALTER TABLE `t1` DEFAULT CHARSET=utf8, MODIFY COLUMN…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…table is rebuilt and values will be more sensible: mysql> alter table antest_innodb type=innodb; Query…column “c” to NULL in both tables and see how values change: mysql> update antest set c=NULL; Query…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…’ll need in those aggregate tables, and include columns to support these queries. But… of your application in advance. What about partitioning in MySQL 5.1? …you from the downtime caused by ALTER TABLE — a typical reason to think… you add them. Did this gain us anything? We dropped one column,…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
… different question. MySQL Optimizer does good job in this case doing full table scan only if it is needed because column can be NULL. Now lets try few more queries: mysql… alter table fact drop key i, add key(i,val); Query OK, 7340032 rows affected (37.15 sec) Records: 7340032 Duplicates: 0 Warnings: 0 mysql…
