June 19, 2013

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

…mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: AUTO_INCREMENT and MERGE TABLES

September 11, 2008 By Leave a Comment

… cause auto_increment value to be reused too. Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not: mysql> alter table am auto_increment… MyISAM tables works: mysql> alter table a1 auto_increment=100; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> alter table a2 auto_increment=100…

Filed Under: Insight for DBAs Tagged With: ,

Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE

November 29, 2011 By 8 Comments

… you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A…) Now check the auto_increment counter: show create table foo\G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int… a customer. So, if this saves you from doing an ALTER TABLE to change the auto incremental column size, then send him a beer ;)

Filed Under: MySQL

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

… row table which would look as following in terms of data and index size: mysql> show table …: 0 Index_length: 1460322304 Data_free: 7340032 Auto_increment: 10000001 Create_time: 2012-06-27 13…re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget…

Filed Under: Insight for DBAs, MySQL

Post: Hacking to make ALTER TABLE online for certain changes

October 29, 2007 By 26 Comments

…PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take…auto_increment, (2) flush tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards: — 1. mysql> CREATE TABLE `huge_table…

Filed Under: Insight for DBAs Tagged With: , ,

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….00 sec) session2 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , , ,

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…MySQL manual, especially the option and variable reference table…auto_increment_incrementblogpercona.commanual auto_increment…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… table that has never had a full-text index on it, and then I create one, the following warning is generated: mysql> alter table…, you’ll get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166… a time. If you try it, this is what happens: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!

December 9, 2010 By 18 Comments

…mysql> alter table a drop key c; Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize table a; +——–+———-+———-+——————————————————————-+ | Table…

Filed Under: Insight for DBAs, MySQL Tagged With: , ,

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

… NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from idxitest where a=5 and… and should not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK…

Filed Under: Insight for DBAs, MySQL Tagged With: ,
« Older Posts