… using MyISAM for everything!. MyISAM may be good for raw performance, but there are limitations which MyISAM imposes that are difficult to work with. MyISAM… fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) … TokuDB performs if PK is (`id`,`hid`,`mid`). This also will affect select performance, …MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
MyISAM storage engine has key compression which makes its indexes much smaller, allowing better fit in caches and so improving performance dramatically. Actually packed indexes…) 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… is almost 3 times faster. With PACK_KEYS=1 we got join by integer key performing almost 5 times slower than it was…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
…1 select_type: SIMPLE table: t2 type: ref possible_keys: val key: val key_len: 4 ref: const rows: 4092 Extra: *************************** 2. … index structure for such data distribution but good enough for performance gotcha illustration purposes. The benchmark shows following results for MyISAM …
Post: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 5, 2009 By 26 Comments
…performance…indexes: KEY `Year` (`Year`,`Month`), KEY `Year_2` (`Year`,`DayOfWeek`), KEY `DayOfWeek` (`DayOfWeek`,`Year`,`DepDelay`), KEY `DestCityName` (`DestCityName`,`OriginCityName`,`Year`), KEY `Year_3` (`Year`,`DestCityName`,`OriginCityName`), KEY…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
…. MyISAM uses key-compression for VARCHAR columns, so makes things worse). So to have good performance for both case we should leave both indexes: KEY `state_id` (state_id) and KEY `state_id_2` (state_id, city, address) The drawback is worse INSERT performance, here…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…index key parts as soon as there is a range clause on the previous key part. So if you have INDEX…index will be used which can be seriously affect performance. Of course in this example you can use index…) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `d` (`d`,`group_id`) ) ENGINE=MyISAM AUTO_INCREMENT=…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… integer keys Performance degradation can range from few percent to couple of times for Innodb tables MyISAM Tables may suffer significantly if key compression… bound workload results are likely to be different as longer indexes expected to have much worse cache fit, especially if you…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… not needed pas part of second key as PRIMARY key is appended to it internally anyway. For MyISAM table you should use K… of indexes I prefer to add indexes only in cases which provides positive impact to performance. At some point the gains from indexes you add will be less than performance loss because of having too many indexes. Q…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
… guaranty you will never run into them. Performance Benchmarks – Innodb and MyISAM have different performance properties and you can’t really say one…. Full text search indexes, GIS, multi-column auto increment keys are great examples. There are also different limits for MyISAM and Innodb – it…
