… ALTER TABLE performance with fast index creation and without it with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table…. I used 10mil row table which would look as following in terms of data and index size: mysql> show table status like “sbtest…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…) ) ENGINE=INNODB; trying to do something like “ALTER TABLE bar DROP j” or “ALTER TABLE bar MODIFY COLUMN j j SMALLINT NOT NULL” will produce… in a new session. We move on to table 2: (root@localhost) [oops4]> alter table dos2 change column fkto3 fkto3 char(32) character… I think there’s something of an inconsistency present when MySQL won’t allow us to change an FK-referenced column…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… | Waiting for table metadata lock | ALTER TABLE test_2 DROP COLUMN c | | 7 | msandbox | localhost | test | Query | 195 | Waiting for table metadata lock | select… table that is already in the Table Cache, any query to a table that has a DDL operation waiting, will block. Why? Because MySQL… reopen the modified table and there will be new entries in the Table Cache. Let’s see this phenomenon in action: session6 > ALTER TABLE test…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
… how MySQL‘s Transactions work with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table with modified structure… –single-transaction dumptest > dump.sql SESSION1: (before dump has completed) mysql> alter table C add i int not null; Query OK, 1 row…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… costs? Let’s take the employees database, and slightly modify the employees tables: mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB…. Now let’s modify the query by selecting only the first_name field instead of selecting all the fields: mysql> explain SELECT…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t MODIFY…) mysql> ALTER TABLE t MODIFY v VARCHAR(8); Query OK, 4194304 rows affected (1 min 13.69 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE…
Post: Converting Character Sets
March 17, 2009 By 22 Comments
… TABLE `t1` ( ->Â Â `c1` text NOT NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE… to be. Consider the following ALTER statement against the table in Approach #1: ALTER TABLE `t1` DEFAULT CHARSET=utf8, MODIFY COLUMN `c1` text CHARACTER SET…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 8 Comments
…/var/lib/mysql/recover/ chown mysql.mysql /var/lib/mysql/recover/$CURRENT_TABLENAME.ibd sleep 1 echo “importing tablespace” mysql -u root -e “alter table recover.$CURRENT…extra column. With a modified schema, the recovery completed. The customer was able to download the tables in MyISAM format and …
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
… or MySQL 5.5) you can now leverage table compression by specifying ROW_FORMAT=COMPRESSED. In our case we only wanted MySQL to… CREATE TABLE or ALTER TABLE statements. In our case, we chose to re-build the table using ALTER TABLE via a null-operation like this: ALTER TABLE `t1… 2.1 in order to modify the table if you cannot sustain the blocking effects of a traditional ALTER TABLE statement. I hope this…
Post: Filtered MySQL Replication
November 7, 2007 By 8 Comments
… fan of filtered or partial MySQL Replication (as of version MySQL 5.0) – there is enough …as there is no any real data to modify. Daniel Schneller wrote pretty good tutorial on …tables to be replicated to the slaves. Another issue is of course creating and altering tables. If you have Innodb tables…
