….test # ######################################################################## # test.test # ######################################################################## # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id…
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: 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: 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: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… compression instead of implementing “classical” approach of index prefix compression ? HT: The reason is that index prefix compression prevents one from using a binary… InnoDB’s adaptive hash indexes require that the prefix is stored in every record R. An adaptive hash index tries to guess where…
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: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… to note MySQL only saves very basic cardinality information for index prefixes for index stats and these rarely change. There is no histograms… but instead we get very wrong cardinality which shows us index prefix is unique (245900 is estimate for the row count in…. The “i” key cardinality is overestimated 3 times, “c” key prefix cardinality is underestimated 5 times and the combined (c,i…
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: 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…
