June 19, 2013

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

May 31, 2013 By Leave a Comment

…unique or a foreign key. This is the output of our tool when run on the same table…duplicate-key-checker, but it does not detect all cases. For example: mysql> alter table test… this duplicate clustered index, execute: ALTER TABLE `test`.`test` DROP INDEX `redundant`, ADD INDEX `redundant` (`col2`); #…

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

Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency

May 22, 2013 By Leave a Comment

… schema. A star schema presents some unique challenge to the database optimizer. The … hot test. All OS caches are dropped at this time as well. These …ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql…

Filed Under: Benchmarks, 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…

Filed Under: Insight for DBAs, MySQL

Post: Best kept MySQLDump Secret

March 23, 2012 By 13 Comments

…MySQL‘s Transactions work with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table…

Filed Under: Insight for DBAs, MySQL

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing foreign keys…

Filed Under: Benchmarks, Insight for DBAs, MySQL

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

February 26, 2013 By 1 Comment

…a unique key as they suggest, your data won’t load due to unique key …dropping FT indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER TABLE… happens: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY (details); ERROR…

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

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> show create table t\G [...] KEY `name` (`name`), KEY `name_2` (`name`), KEY…

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

Post: Find unused indexes

June 30, 2012 By Leave a Comment

…ALTER TABLE `tpcc`.`order_line` DROP 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: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

…table as ANALYZE did not run yet. mysql> show index from antest; +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…upgrading to 5.6 and running ALTER TABLE foo ENGINE=InnoDB. In theory, yes….size to 1, dropped/rebuilt the InnoDB index, and tried again. mysql: SELECT id,…, although Japanese poses its own unique problems for FTS of any …your first FULLTEXT KEY to an InnoDB table, be prepared for a table rebuild. …

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