June 19, 2013

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

May 31, 2013 By Leave a Comment

… that use MySQL, for which we at Percona tend to find redundant indexes. And second, because in some cases, we may need to have… includes mysqlindexcheck, similar to pt-duplicate-key-checker, but it does not detect all cases. For example: mysql> alter table test add index redundant (col2…

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

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL …ALTER proceeds through successfully: session1 > rollback; Query OK, 0 rows affected (0.00 sec) session2 > ALTER TABLE test add…not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE…

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

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

June 27, 2012 By 4 Comments

…())); and added key on column C: alter table sbtest add key c(c); The box I’m using for test is …. I used 10mil row table which would look as following in terms of data and index size: mysql> … and Dropping column for 128M buffer pool, sized to illustrate the case of index being significantly larger …

Filed Under: Insight for DBAs, MySQL

Post: MySQL Upgrade Webinar Questions Followup

June 28, 2012 By 7 Comments

…to keep into account possible query changes, replication etc. Q: Is it possible to move MyISAM to Innodb through plain ALTER…Q: What are the key concerns upgrading MySQL 4.1 to MySQL 5.5? This…used in production) which adds the difference compared to your production use. If resources allow I prefer to…

Filed Under: Insight for DBAs, MySQL

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…MySQL will avoid to create duplicate indexes: mysql> alter table t add index key_for_first_name(name); Query OK, 0 rows affected (0.01 sec) mysql> alter table t add index key_for_first_name(name); ERROR 1061 (42000): Duplicate key name ‘key_for_first_name’ Using custom names with…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: Should you name indexes while doing ALTER TABLE ?

May 28, 2008 By 10 Comments

… to it, for example: mysql> create table t1(i int, j int); Query OK, 0 rows affected (0.01 sec) mysql> alter table t1 add key… – you can’t easily see what is the index MySQL trying to use from explain, such as if you have some smart index… would specify index name MySQL will complain if you try to create index with same name again: mysql> alter table t1 add key idx_i(i…

Filed Under: Insight for DBAs Tagged With:

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

February 26, 2013 By 1 Comment

… whom the prudent move would be a migration to InnoDB, but due to their use of MyISAM FTS, the idea of a complete… to CREATE more than one at a time. If you try it, this is what happens: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY (details); ERROR 1795 (HY000): InnoDB…

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

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

…mysql: mysql> use test Database changed mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql…mysql> alter table test_concat add data varchar(10); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter…

Filed Under: MySQL

Post: Find unused indexes

June 30, 2012 By Leave a Comment

…to use the benchmark software tpcc-mysql. After some runs we can start to analyze the information on INDEX_STATISTICS table. mysql…KEY `fkey_order_line_2`; — type:non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER TABLE `tpcc`.`stock` DROP KEY…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… then add the rest later, reloading the data if needed. Aggregate Aggregation is absolutely key for…from the downtime caused by ALTER TABLE — a typical reason to think about master-master …to MySQL, although we’re most famous for our knowledge about it.  When another tool is the right one, we use…

Filed Under: Insight for Developers Tagged With: , , , ,
« Older Posts