… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 3 Comments
… 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…(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… slower for packed keys Be careful with PACK_KEYS=1 as it can slow down your integer joins a lot Be careful with PACK_KEYS=0…
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… 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
…: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 262144 Extra: *************************** 2. row… as MyISAM uses key compression for varchar columns so random key lookups become significantly slower. I tried to set pack_keys=0 which typically…
Comment: Why MySQL could be slow with large tables ?
May 27, 2009 By 139 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… it uses JOIN perfectly. id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE stat range dateline dateline…
Comment: To pack or not to pack - MyISAM Key compression
October 26, 2008 By 3 Comments
pack_keys doesn’t seem to do anything for me (MySQL 5.0). I have table with index in reverse order (primary key… still doesn’t work – all I get is “126 Incorrect key file for table”.
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: Working with large data sets in MySQL
July 18, 2007 By 12 Comments
I’m using InnoDB tables, so I guess I can’t use PACK_KEYS? Is there any other way to go about overcoming this problem? Could you perhaps do a blog on this very topic? I think many sites suffer this kind of dilemma.
Comment: Using CHAR keys for joins, how much is the overhead ?
June 19, 2007 By 18 Comments
…, I did not test that because I could not disable packed keys for MyISAM and I assumed this would be majority of…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment…
