….test # ######################################################################## # test.test # ######################################################################## # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… have to make between having prefix index – which can be significantly smaller in size and having index being covering index, which means query can… Now lets see if index can be used as covering index if it has some key parts which are prefixes: mysql> explain select… use index which has prefix key parts as covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… with a prefix of the clustered index [...] # To shorten this duplicate clustered index, execute: ALTER TABLE `test`.`t` DROP INDEX `name`, ADD INDEX `name` (`name… with a prefix of the clustered index [...] # To shorten this duplicate clustered index, execute: ALTER TABLE `test`.`t` DROP INDEX `age`, ADD INDEX `age` (`age…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… popular index type in MySQL – BTREE can speed up equality and prefix range matches. So if you have index on (A,B) This index… is not index prefix. It is important to look at key_len column in explain plan to see how many index parts are… B=5 for the same index MySQL will use the index… but it will only use A prefix for row lookups and scan…
Post: Blob Storage in Innodb
February 9, 2010 By 13 Comments
… for Innodb – BLOB can have prefix index and it was easier to implement index BLOBs if their prefix is always stored on the row… can have prefix index but this no more requires column prefix to be stored on the page – you can build prefix indexes on blobs which…_SIZE is not specified and compression for normal data and index pages is not enabled. If you’re interested to learn…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…. That would clearly save indexing space. Is this a reasonable performance optimization. (Keep in mind that the prefix adaptive hashing would fail here, because the first 10 or so characters usually are the same). Of course UNIQUE index… setup for 5 indexes to differ only by first column. Regarding changing primary key to include such column prefix it depends on…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… exceptions) BTREE indexes – this index type is very good to be able to quickly lookup the data on any its prefix and traversing ranges between values in sorted order. For example when you query AGE=18 with single column BTREE index… than if only index prefix is used. MySQL does not know it as it looks at stats from different indexes independently not trying…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
…, index (A,B) is not duplicate to index (B,A) So now what are Redundant indexes when ? I call redundant indexes BTREE indexes which are prefix of other index, for example…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
…) unsigned NOT NULL, `revert_domain` varchar(255) NOT NULL, `url_prefix` varchar(255) NOT NULL, `from_domain_id` int(10) unsigned…_site_id`,`message_published`), KEY `revert_domain` (`revert_domain`,`url_prefix`(80)), KEY `from_site_id` (`from_site_id`,`message_published… this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor should…
