…; – to design proper indexes is additional research, especially when MySQL optimizer is not smart in picking best one. The really nice… MyISAM and TokuDB in empty table with created indexes. Load time for MyISAM: 16608 sec For TokuDB: 19131 sec Datasize (including indexes) MyISAM…-2.1.0 for both MyISAM and TokuDB tests. And if you are interested to compare MyISAM with previous engines: QueryMyISAMMonetDBInfoBrightLucidDBInfiniDB Q072…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… it with basics of Hardware OS And Application optimization. I call this Innodb Performance Optimization Basics so these are general guidelines which… MySQL Presentations. Application tuning for Innodb Especially when coming from MyISAM background there would be some changes you would like to…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… large portion of my life working on MySQL Performance Optimization and so MySQL Optimizer is quite important to me. For probably last…. The solution is simple – make optimizer pluggable and make it possible to stick to old optimizer behavior with new MySQL Version. Make… their database 100% in RAM (even for Disk tables such MyISAM or Innodb) others keep database on SSD which has completely…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… had discussion today which involved benchmarks of Join speed for MyISAM and Innodb storage engines for CPU bound workload, this is… as data is clustered together with index and generally highly optimized Innodb builds hash indexes which helps to speed up lookup… it perform slower for random lookups MyISAM generally has lower processing overhead due to its simplicity MyISAM still a bit better by…
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
… data with SQL statements. I wanted to see how fast MyISAM vs. MonetDB would interpret really large INSERT statements, the kind… size of the resulting table on disk (smaller is better): MyISAM is 787MB, MonetDB is 791MB, and Infobright is 317MB. Next… into MySQL. It’s a complete server with a different optimizer, etc. This point was hammered home during the LOAD DATA…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… as well as IN lists make this optimization impossible, not even speaking about index merge optimization. Lets look at this example: CREATE… NULL, KEY `c1` (`c1`,`ord`), KEY `c2` (`c2`,`ord`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> explain select * from utest where c1…. Once this is implemented similar approach could be used for optimizing ORDER BY with IN. This original query (in memory data…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… Performance Forum as well as from our customers regarding query optimization… which had one thing in common – It is not query which needed to be optimized. Way too frequently people design schema first and then think…. COUNT Many people think counting is fast, some have heard MyISAM is instant with COUNT(*) without reading enough details to know…
Post: MySQL optimizer: ANALYZE TABLE and Waiting for table flush
February 27, 2013 By 3 Comments
The MySQL optimizer makes the decision of what execution plan to use based … original query. On the other hand, if the table is MyISAM there will be no rollback process so all the already… problem of ANALYZE. Other commands like FLUSH TABLES, ALTER, RENAME, OPTIMIZE or REPAIR can cause threads to wait on “Waiting for…
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
The more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to …(10) unsigned NOT NULL, `slack` varchar(50) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… to be cached — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous… | 2×2048 KB I have and old table which was not optimized for prolonged period of time, so it reflect natural state…
