…(20) NOT NULL default ”, KEY `c` (`c`), KEY `id` (`id`) ) ENGINE=MyISAM Index size: PACK_KEYS=DEFAULT – 1550K PACK_KEYS=1 – 1453K PACK_KEYS=0 – 8176K As we can… PACK_KEYS=DEFAULT Q1: 3.50s Q2: 0.35s Q3: 0.12s Q4: 1.26s PACK_KEYS=0 Q1: 1.25s Q2:0.40s Q3: 0.14s Q4: 0.16s PACK_KEYS=1 Q1: 3.50s Q2:1.55s Q3: 0.12s Q4…
Post: Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
… mutex which is held for the time of key_block being copied from key_cache to the thread local space. Happily MyISAM… tenfold and performance almost doubled. Another interesting result was – adding PACK_KEYS=0 to most actively updated tables almost doubled throughput again (and… this option has but I did not expect modification of packed keys would be so much more expensive. After doing such changes…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…: ref possible_keys: j key: j key_len: 4 ref: test.t1.i rows: 26 Extra: 2 rows in set (0.00 sec) So….0 seconds which is over 6 times slower than joining on the integer. In fact this was expected as MyISAM uses key compression for varchar columns so random key lookups become significantly slower. I tried to set pack_keys=0 which typically helps…
Comment: To pack or not to pack - MyISAM Key compression
November 2, 2012 By 4 Comments
… information. I have done a test to turn all the pack_keys in a Discuz! database into 1, in a middle size… also have a question, that you said “Be careful with PACK_KEYS=0 as it can blow up your index size 10x times… I am confused with a table, whose index declines when PACK_KEYS=0(changed from DEFAULT). MyISAM seems to set a limit to…
Comment: Working with large data sets in MySQL
July 18, 2007 By 12 Comments
Right. You can’t use large limits because skipping rows becomes expensive. Also DESC can be slower for MyISAM Tables with key compression you can set PACK_KEYS=0 for this table and check if it helps. Anyway this becomes rather offtopic for this post 
Comment: Why MySQL could be slow with large tables ?
May 27, 2009 By 141 Comments
… NULL, PRIMARY KEY (`id`), KEY `bannerid` (`banner_id`), KEY `dateline` (`dateline`), KEY `ip_interval` (`ip_interval`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT… dateline<1243466944 GROUP BY code ORDER BY count DESC LIMIT 0, 10 This query takes ~13 seconds to run (2GHZ Dual…
Comment: To pack or not to pack - MyISAM Key compression
October 26, 2008 By 4 Comments
pack_keys doesn’t seem to do anything for me (MySQL 5.0). I have table with index in reverse order (primary key(col… still doesn’t work – all I get is “126 Incorrect key file for table”.
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… Warnings: 0 mysql> show index from antest; +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Comment: ORDER BY ... LIMIT Performance Optimization
November 17, 2009 By 100 Comments
… | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +——–+————+———-+————–+————–+———–+————-+———-+——–+——+————+———+ | follow | 0 | PRIMARY | 1…
