… going to work and MySQL will need to scan keyblock from the start uncompressing keys to find matching key value. This means the… can pack it” The only settings available is PACK_KEYS=0|1|DEFAULT. Value 0 disables compression for all keys. Value 1 forces it for all keys. Value DEFAULT will use key compression…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
… tenfold and performance almost doubled. Another interesting result was – adding PACK_KEYS=0 to most actively updated tables almost doubled throughput again… I would expect to see it more than double if all tables would be converted with this option. This is of… expect modification of packed keys would be so much more expensive. After doing such changes we got insert rate to MySQL close to…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select … select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows:…key compression for varchar columns so random key lookups become significantly slower. I tried to set pack_keys…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… for fully sequential access, jam packed rows – quite possible scenario for …scanned. There are also clustered keys in Innodb which combine index …accesses. This problem exists for all kinds of applications, however for…MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…MySQL configuration, we quite … only as big as needed (key_buffer_size). There are many examples… 14176 /***************************************************************************** 14177 Fill join cache with packed records 14178 Records are stored … needed. And it’s allocated all at once. So a 128M …
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…at all …mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed…
Comment: To pack or not to pack - MyISAM Key compression
November 2, 2012 By 4 Comments
… gain important information. I have done a test to turn all the pack_keys in a Discuz! database into 1, in a middle…. That is when I became confused about the introduction of MySQL, saying it will be faster for reading, which is what… I am confused with a table, whose index declines when PACK_KEYS=0(changed from DEFAULT). MyISAM seems to set a limit…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… solution for us was to move out all databases out of MySQL directory to other location, check … you hoped for causing unexpected slowdowns. delay_key_writes Whatever way you enable this option – globally…can be even better. Read only data, especially packed with myisampack. This can’t be corrupted …
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
…are features MySQL should get for all storage engines at some point. Descending indexes – This is something MySQL does … Or some storage engines, such as MyISAM (for packed indexes) may have reverse scan being much … covered query you might rather shorten your key to KEY(A,B) to keep index smaller. …
