… WITH READ LOCK is waiting for that very nasty “full join” select to complete. What is worse as the statement started…) Reads though would not be blocked, ie there is no MYISAM like table lock priority problem with pending WRITE query blocks…’re just using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… MySQL 5.1.18 using MyISAM and Innodb tables. This time unlike other benchmarks I decided to do Join not on primary key… ? Joining on Char columns completes in 4.5 seconds on Innodb which is about 50% slower compared to Joining on Int, for MyISAM however… to couple of times for Innodb tables MyISAM Tables may suffer significantly if key compression is not disabled Joining on Shorter CHAR keys…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
We had discussion today which involved benchmarks of Join speed for MyISAM and Innodb storage engines for CPU bound workload, this is when… 1.19s Innodb 0.07s 0.30s 0.38s As you see in such circumstances Innodb is actually faster than MyISAM in 2 cases out of 3. I guess the reasons are the following: Innodb primary key joins…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… KEY (id), KEY state_id (state_id) ) ENGINE=MyISAM; 4) Dictionary table for cities_join: CREATE TABLE IF NOT EXISTS `states` ( `id… times faster than for MyISAM. This is great example of the case when Innodb is much faster than MyISAM for Read load. The other surprise could be almost zero cost of the join, which…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 3 Comments
… not a bit longer rows is frequent reason of MyISAM performing better than Innodb. In this article I’ll get in a… hack 
The fact MyISAM will by default compress string keys but not integer keys is the biggest reason joins using string keys… may give great space savings. Defaults for MyISAM are good for most cases Avoid joining on strings, it is much slower anyway…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…. As FullText currently works with MyISAM tables these are MyISAM related. join_buffer_size Buffer used for joins without indexes and few other cases… as Innodb Buffer Pool, Key Buffer etc. It works for certain storage engines which support large pages in general. Currently MyISAM and Innodb…
Post: Doing Re-run of Migrating MyISAM to Innodb Webinar
December 1, 2010 By 9 Comments
… which prevented from all registered attendees to be able to join webinar. I personally would be very upset having booked my… join at the time of event (which is frankly exactly what happened to me). As result we have scheduled Migrating MyISAM to Innodb…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed… one of our MySQL Presentations. Application tuning for Innodb Especially when coming from MyISAM background there would be some changes you would… to use it in joins), large unpacked indexes (try to be easy on indexes). With these basic innodb performance tunings you will…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… clause and joins. It is important for tables which is being read to be Innodb – even if writes are done in MyISAM table… not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which… unsafe fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog option with caution. Note disabling binary…
Post: XtraDB/InnoDB CPU bound benchmarks on 24cores server
December 29, 2008 By 6 Comments
…=MYISAM ft_min_word_len=4 innodb_additional_mem_pool_size=16M innodb_buffer_pool_size=15G innodb_data_file_path=ibdata1:10M:autoextend innodb…_size=512M innodb_status_file=0 innodb_thread_concurrency=0 innodb_io_capacity=1000 innodb_write_io_threads = 16 innodb_read_io_threads = 16 join_buffer…
