… 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: 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
… going to work and MySQL will need to scan keyblock from the start uncompressing keys to find matching key value. This means the…(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
… rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts up to 100K of records/sec which… tenfold and performance almost doubled. Another interesting result was – adding PACK_KEYS=0 to most actively updated tables almost doubled throughput again… 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
…` int(10) unsigned NOT NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j… explain if someone curious, it did not really change beside key lengths: mysql> explain select sum(t1.i+t2.j+length(t2… 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: 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(col… still doesn’t work – all I get is “126 Incorrect key file for table”.
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… per second for fully sequential access, jam packed rows – quite possible scenario for MyISAM tables. … ranges are scanned. There are also clustered keys in Innodb which combine index access with data…. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO …
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…MySQL configuration, we quite often want to know… are allocated only as big as needed (key_buffer_size). There are many examples of … source: 14176 /***************************************************************************** 14177 Fill join cache with packed records 14178 Records are stored in tab->…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…0 mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null…
Comment: ORDER BY ... LIMIT Performance Optimization
November 17, 2009 By 93 Comments
… are set:- mysql> show index from follow; +——–+————+———-+————–+————–+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null…
Post: Using MyISAM in production
June 17, 2006 By 35 Comments
… into trouble. Just one is enough. Lock priorities. By default MySQL treats updates as higher priority operations. You can use SELECT… flushing dirty index blocks from key_buffer to disk, but it also comes at great danger if MySQL Server crashes or power… storage engine can be even better. Read only data, especially packed with myisampack. This can’t be corrupted or updated and…
