…# `col3` varchar(200) default null # To remove this duplicate index, execute: ALTER TABLE `test`.`test` DROP INDEX `col2`; # ######################################################################## # Summary of indexes # ######################################################################## # Size…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…` ( `INDEX_ID` bigint(20) unsigned NOT NULL, `POS` int(10) unsigned NOT NULL, `COL_NAME` varchar(255) DEFAULT NULL, PRIMARY KEY (`INDEX_ID`,`POS`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Fields names explain their content. Index id in…
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 rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query… expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t MODIFY v VARCHAR(7); Query…
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…, `url_title` varchar(255) NOT NULL, `isexternal` tinyint(3) unsigned NOT NULL, `revert_domain` varchar(255) NOT NULL, `url_prefix` varchar(255) NOT…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
…` varchar(64) NOT NULL DEFAULT ”, `email` varchar(64) NOT NULL DEFAULT ”, `password` varchar(64) NOT NULL DEFAULT ”, `dob` date DEFAULT NULL, `address` varchar(255…-compression for VARCHAR columns, so makes things worse). So to have good performance for both case we should leave both indexes: KEY…
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…(mean): 0.082196 2) Results for VARCHAR: select SQL_NO_CACHE city from cities_varchar WHERE state=’Minnesota’ limit 10000,5; Result… quite expected – This is MyISAM table which is accessed via index, which means to retrieve each row MySQL will have to…
Post: Why Index could refuse to work ?
September 8, 2006 By 15 Comments
… at article table: CREATE TABLE `article` ( `article_id` varchar(20) NOT NULL, `dummy` varchar(255) NOT NULL default ‘dummy’, PRIMARY KEY (`article… article_id is VARCHAR and this is the problem. Comparing String to Number is not going to use the index. Lets check… for many reasons, but if you decided to go with VARCHAR (ie you need leading zeroes to be preserved) you should…
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… about performance impact of extending index is when you increase its length dramatically, for example adding long varchar column. In such cases… presentation. Q: what is the impact on indexing to use wider UUID such as VARCHAR(36) instead of auto-increment A: If…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is very good to be able…) unsigned NOT NULL, `i2` int(10) unsigned NOT NULL, `val` varchar(40) DEFAULT NULL, KEY `i1` (`i1`), KEY `i2` (`i2`), KEY… to use second index and hence index merge, what does it turn to ? It is not combined index but single index on another column…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed_colB = Y” might use the index merge union… important, as we will see below) scan the indexes on indexed_colA and indexed_colB and then do a set-theoretic union of… DEFAULT 0, username VARCHAR(20), … other columns here … PRIMARY KEY(user_id), INDEX `parent_id` (parent_id), INDEX `status` (status), INDEX `user_type` (user…
