… MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX …varchar(200) default null # To remove this duplicate index, execute: ALTER TABLE `test`.`test` DROP INDEX `col2`; # ######################################################################## # Summary of indexes…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… varchar(255) DEFAULT NULL, PRIMARY KEY (`INDEX_ID`,`POS`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Fields names explain their content. Index id in our example is 1679: mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679; +———-+—–+———-+ | INDEX_ID | POS …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) …) 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…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… multi-column index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is… spoke about how MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL gets a…) unsigned NOT NULL, `i2` int(10) unsigned NOT NULL, `val` varchar(40) DEFAULT NULL, KEY `i1` (`i1`), KEY `i2` (`i2`), KEY…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed…DEFAULT 0, username VARCHAR(20), … other columns here … PRIMARY KEY(user_id), INDEX `parent_id` (parent_id), INDEX `status` (status), INDEX `user_type` (…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously…) NOT NULL, `person_role_id` int(11) DEFAULT NULL, `note` varchar(250), `nr_order` int(11) DEFAULT NULL, `role_id` int… you need more manual care and tuning now. MySQL is conservative about “Using index” -in most cases it will be the right…
Post: Common MySQL traps webinar questions followup
January 17, 2013 By 1 Comment
…the master, so they may benefit from different indexing Q: Is there a Percona recommendation for … ‘canceled’ or ‘archived’. If you use a string, a VARCHAR(15) could be good to store such values …: Are these recommendations true of all versions of MySQL? If not which versions? The recommendations I …
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… buffer pool and the other secondary indexes have been pushed out of the buffer pool. mysql> select * from information_schema.innodb_buffer…_Name varchar(25), C_Address varchar(25), C_City varchar(10), C_Nation varchar(15), C_Region varchar(12), C_Phone varchar(15), C_MktSegment varchar(10…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t MODIFY v VARCHAR(2… sec) mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t MODIFY v VARCHAR(4… dataset: mysql> SET expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t MODIFY v VARCHAR(7…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…indexes) # du -sh * 203M ibdata1 128M ib_logfile0 128M ib_logfile1 988K mysql…
