June 19, 2013

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

May 22, 2013 By Leave a Comment

…star schema presents some unique challenge to the …All OS caches are dropped at this time …from the buffer pool should be faster than getting them from…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: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…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…mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY…

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

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…FROM t; Query OK, 2097152 rows affected (10.11 sec) Records: 2097152 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t ADD KEY…UNIQUE indexes in 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…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Best kept MySQLDump Secret

March 23, 2012 By 13 Comments

…from the fact how 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: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM…6 and running ALTER TABLE foo ENGINE=InnoDB. In…dropped/rebuilt the InnoDB index, and tried again. mysql… poses its own unique problems for …KEY to an InnoDB table, be prepared for a table…

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

Post: Working with large data sets in MySQL

July 5, 2007 By 12 Comments

… with large data things in MySQL is patience and careful planning. …from backup. You need Patience simply because things are going to take a lot of time. Think about 500GB table for example – ALTER TABLE… primary key and unique indexes using keycache) If it does not performance may drop performance…

Filed Under: Insight for Developers Tagged With: ,

Post: Knowing what pt-online-schema-change will do

November 1, 2012 By 1 Comment

…table with a new column named “foo”.  Notice that the table has a PRIMARY key and another non-unique key…guest_country`, `score` FROM `test`.`t`…Dropping new table… DROP TABLE IF EXISTS `test`.`_t_new`; Dropped new table OK. Notice that the new, altered table created at the beginning is dropped…MySQL…

Filed Under: Insight for DBAs, Percona Software 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

Comment: Duplicate indexes and redundant indexes

January 19, 2012 By 38 Comments

…alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid’: needed in a foreign key constraint ============= Below is scenario. drop table…

Filed Under: Insight for DBAs Tagged With:

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…doesn’t save you from the downtime caused by ALTER TABLE — a typical …as many rows as you have unique ads per day.  Viewed…primary key, we can shrink the primary key‘s width: create table ads_… this gain us anything? We dropped one column, and we …using MySQL 5.1′s row-based replication, or in MySQL 5…

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