June 19, 2013

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

May 31, 2013 By Leave a Comment

….6 brought: GTID-based replication, InnoDB Fulltext, Memcached integration, a more complete…varchar(200) default null # To remove this duplicate index, execute: ALTER TABLE `test`.`test` DROP INDEX `col2`; # ######################################################################## # Summary of indexes…

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

Post: How to recover table structure from InnoDB dictionary

April 22, 2013 By 4 Comments

… is not defined InnoDB will use a unique secondary index as the primary. If there is no any unique index InnoDB will create one…, `COL_NAME` varchar(255) DEFAULT NULL, PRIMARY KEY (`INDEX_ID`,`POS`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Fields names explain their content. Index id in…

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

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… affected when turning expand_fast_index_creation on. Here and in later examples I’m extending the VARCHAR column to trigger table… must be indexed, so it is impossible to temporarily drop the corresponding index; mysqldump –innodb-optimize-keys ignores the first UNIQUE index on non…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Statistics of InnoDB tables and indexes available in xtrabackup

September 14, 2009 By 6 Comments

… VARCHAR – since you need to estimate average length) on count of records. And it still would be quite inaccurate as secondary indexes… NOT NULL auto_increment, `url_from` varchar(255) NOT NULL, `url_to` varchar(255) NOT NULL, `anchor` varchar(255) NOT NULL, `from_site… feature to address this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor…

Filed Under: Percona Software Tagged With: , ,

Post: Testing InnoDB "Barracuda" format with compression

April 23, 2008 By 3 Comments

…-00-00 00:00:00′, `subject` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `url` varchar(255) NOT NULL, `num_links` smallint… 30286M. To create InnoDB compression table I use ENGINE=InnoDB KEY_BLOCK_SIZE=8 and to test fast index creation I create table only with primary key, without additional indexes. Numbers…

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

…innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE `innodb_myisam_stopword` ( `value` varchar(18) NOT NULL DEFAULT ” ) ENGINE=InnoDB…I reset innodb_ft_min_token_size to 1, dropped/rebuilt the InnoDB index, …

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

Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1

January 8, 2007 By 117 Comments

…indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(64) NOT NULL default ”, `email` varchar… / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-…

Filed Under: Benchmarks Tagged With: ,

Post: Redundant index is not always bad

August 28, 2007 By 7 Comments

… we use for such queries is covering index, which store all needed columns in the index, and there is no need to…. (This is for MyISAM, for InnoDB difference will be less. MyISAM uses key-compression for VARCHAR columns, so makes things worse). So to have good performance for both case we should leave both indexes: KEY…

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

Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?

January 24, 2008 By 30 Comments

…) Table with VARCHAR: CREATE TABLE cities_varchar ( id int(10) unsigned NOT NULL auto_increment, state varchar(50) NOT NULL, city varchar(255) NOT… quite expected – This is MyISAM table which is accessed via index, which means to retrieve each row MySQL will have to… is also interesting to note performance of Innodb tables in this case: for VARCHAR it takes about 0.022 per query which…

Filed Under: Benchmarks Tagged With: , , , , , , ,

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…) indexed varchars with an additional bigint column containing the crc64, and change the indexing to be on that column. That would clearly save indexing… presentation. Q: what is the impact on indexing to use wider UUID such as VARCHAR(36) instead of auto-increment A: If… IN-ranges in some cases for better index usage. Q: Is the b+ tree innodb index a single or double linked list at…

Filed Under: Insight for DBAs, MySQL
« Older Posts