… would clearly save indexing space. Is this a reasonable performance optimization. (Keep in mind that the prefix adaptive hashing would fail here… string comparison in MySQL is case insensitive by default while hash comparison will be done case sensitive unless you lowercase string… pages in index order. Note however it is not really requirement for ORDER BY DESC optimization – you can still traverse BTREE in…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…. But InnoDB’s adaptive hash indexes require that the prefix is stored in every record R. An adaptive hash index tries to guess where… scans and index scans more efficient. I can’t wait for ability to create physically sorted indexes with Innodb (with index built by… Innodb policy replacement algorithm takes into account page position in btree index ? HT: No. It is a pure LRU algorithm. PZ: This…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or range… aware which again comes from the fact MEMORY tables use HASH indexes by default. I’ve created rather similar test table: CREATE… true, but there is a reason. HASH index stores list of matching values for each hash value. In this case the key value…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…. Adaptive hash index is hash index Innodb builds for some pages to speed up row lookup replacing btree search with hash search. This section shows hash table size, number of used cells and number of buffers used by adaptive hash index…
Comment: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… sense to have indexes of different types to created on the same column(s) – perfect example is BTREE index and FULLTEXT index, while other combinations may also make sense.” You also might want to have BTREE and RTREE indexes on the same columns or even BTREE and HASH in certain rare cases.
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… going in random locations in index tree which will require a lot of IO in case of index tree not fitting into… disk case with auto_increment having advantage when UUID larger BTREE does not fit in memory any more and so more… BTREE index. This is actually the reason why UUID can do much better than SHA1 as Kevin proposes in the same post – hashes…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
…, integer indexed column and indexed char column. The query is also trivial – the point was to make sure it is not index covered… together with index and generally highly optimized Innodb builds hash indexes which helps to speed up lookup by indexes by passing BTREE index and using hash, which…
Comment: Duplicate indexes and redundant indexes
September 21, 2006 By 38 Comments
… indexes. In other words: if you have two ordered (non-hash) indexes I1, I2 each of the same type T with T in (BTREE… wondering about uniqueness. Suppose we have a unique index UI and a nordinay index I, again of the same type. Then, clearly…, the values in the longer index are unique too, and telling the database that the longer index is unique might help the…
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 45 Comments
… mainly clustered around most recent id values. The table (and index) is much larger than buffer pool size. The first problem… the uu index. This is because UUID() spreads values prefix very well effectively giving almost uniform access to all BTREE. To solve this problem partitioning was a good choice – PARTITION BY HASH (id div 10000000) PARTITIONS…
