title: Why is myisam slower than Innodb
body: <p>There are several Q&amp;A for "Why is InnoDB (much) slower than MyISAM", but I could not find any topic for the opposite.</p>
  
  <p>So I had a table defined as InnoDB wherin I stored file contents in a blob field. Because normally for that MyISAM should be used I switched over that table. Here is its structure:</p>
  
  <pre><code>CREATE TABLE `liv_fx_files_files` (
    `fid` int(11) NOT NULL AUTO_INCREMENT,
    `filedata` longblob NOT NULL,
    `filetype` varchar(255) NOT NULL,
    `filename` varchar(255) NOT NULL,
    `filesize` int(11) NOT NULL,
    `context` varchar(1) NOT NULL DEFAULT '',
    `saveuser` varchar(32) NOT NULL,
    `savetime` int(11) NOT NULL,
    `_state` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`fid`),
    KEY `_state` (`_state`)
  ) ENGINE=MyISAM AUTO_INCREMENT=4550 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>There are 4549 records stored in it so far (with <code>filedata</code> going from 0 to 48M. Sum of all files is about 6G.</p>
  
  <p>So whenever I need to know current total filesize I issue the query</p>
  
  <pre><code>SELECT SUM(filesize) FROM liv_fx_files_files;
  </code></pre>
  
  <p>The problem is that since I switched from InnoDB to MyISAM this simple query lasts really long (about 30sec and longer) whereas on InnoDB it was done in a under one second.</p>
  
  <p>But aggregations are not the only queries which are very slow; it's almost every query.</p>
  
  <p>I guess I could fix it by adopting config (which is currently optimized for InnoDB (only) use), but don't know which settings to adjust. Does anyone have a hint for me please?</p>
  
  <p><a href="https://fx.mobuto.net/a/inw/b8c2c507ab49435babb4aaf894c63f26" rel="nofollow">current mysql server config (<code>SHOW VARIABLES</code> as csv)</a></p>
  
  <p>Example for another query fired on both table types (both contain exact same data and have same definition). All other tested queries behave the same, say run much longer against MyISAM table as InnoDB!</p>
  
  <pre><code>SELECT sql_no_cache `fxfilefile`.`filename` AS `filename` FROM `myisamtable`|`innodbtable` AS `fxfilefile` WHERE `fxfilefile`.`filename` LIKE '%foo%';
  </code></pre>