title: Why is MySQL InnoDB so much slower at full table scans than MyISAM?
body: <p><strong>EDIT</strong></p>
  
  <blockquote>
    <p>OP has acknowledged a mistake when profiling PostgreSQL in his answer below. I am updating this question to reflect the comparison between MyISAM &amp; InnoDB.</p>
  </blockquote>
  
  <p>Hello,</p>
  
  <p>I ran a test against MySQL InnoDB, MyISAM, and PostgreSQL to see how well 
  each of these engines performed doing full table scans to understand what
  the response time might be for the cases where we inevitably
  need to let this happen.</p>
  
  <p>The tests were conducted on an Intel Core 2 Quad Q6600 @ 2.4Ghz w/ 
  4GB RAM and a 7200 RPM HD with a 16MB cache.</p>
  
  <p>MySQL Version was 5.0.67-community-nt-log 32-bit, PGSQL version was 8.4.</p>
  
  <p>I wrote a small script to generate 5 million rows of data in a 4 column table.
  These are the create table statements used in MySQL and PGSQL:</p>
  
  <p>-- InnoDB</p>
  
  <pre><code>CREATE TABLE sample_innodb (
  id integer unsigned not null,
  vc1 varchar(200) not null,
  vc2 varchar(200) not null,
  vc3 varchar(200) not null
  ) ENGINE=InnoDB;
  </code></pre>
  
  <p>-- MyISAM</p>
  
  <pre><code>CREATE TABLE sample_isam (
  id integer unsigned not null,
  vc1 varchar(200) not null,
  vc2 varchar(200) not null,
  vc3 varchar(200) not null
  ) ENGINE=MyISAM;
  </code></pre>
  
  <p>-- PostgreSQL</p>
  
  <pre><code>create table sample_pgsql (
  id integer not null,
  vc1 varchar(200) not null,
  vc2 varchar(200) not null,
  vc3 varchar(200) not null
  );
  </code></pre>
  
  <p>This is the script I used to generate data for these tables:</p>
  
  <pre><code>var chars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXTZabcdefghiklmnopqrstuvwxyz'.split('');
  
  function randomString(length) {
   var str = '';
   for (var i = 0; i &lt; length; i++) {
      str += chars[Math.floor(Math.random() * chars.length)];
   }
  
     return str;
  }
  
  function genrow(idv, vcv1, vcv2, vcv3) {
   return idv + "," + vcv1 + "," + vcv2 + "," + vcv3;
  }
  
  function gentable(numrows) {
   for (var i = 0; i &lt; numrows; i++) {
      var row = 
          genrow(i,
                 randomString(10),
                 randomString(20),
                 randomString(30));
  
      WScript.Echo(row);
    }
  }
  
  gentable(5000000);
  </code></pre>
  
  <p>I ran this script on Windows with the command:</p>
  
  <p><code>cscript.exe /nologo test.js &gt; data.csv</code></p>
  
  <p>You can load this data into MySQL with these commands:</p>
  
  <pre><code>LOAD DATA LOCAL INFILE 'data.csv'
  INTO TABLE sample_innodb
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  (id, vc1, vc2, vc3);
  
  LOAD DATA LOCAL INFILE 'data.csv'
  INTO TABLE sample_isam
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  (id, vc1, vc2, vc3);
  </code></pre>
  
  <p>You can load the data into PGSQL with this command:</p>
  
  <pre><code>copy sample_pgsql (id, vc1, vc2, vc3) from 'data.csv' with delimiter ','
  </code></pre>
  
  <p>I used this query for timing to try and force a worst case table scan scenario:</p>
  
  MySQL
  
  <pre><code>select count(*) from [table] 
  where vc1 like '%blah0%' and vc2 like '%blah1%' and vc3 like '%blah2%';
  </code></pre>
  
  PostgreSQL
  
  <pre><code>select count(*) from [table] 
  where vc1 ilike '%blah0%' and vc2 ilike '%blah1%' and vc3 ilike '%blah2%';
  </code></pre>
  
  <p>I ran this query multiple times to get an average time to complete, leaving
  out the first run to get everything primed in memory.</p>
  
  <p>The results were as follows:</p>
  
  <ul>
  <li>InnoDB - 8.56s</li>
  <li>MyISAM - 1.84s</li>
  <li>PGSQL  -  8.4s </li>
  </ul>
  
  <h2>Question</h2>
  
  <p>Why is it that InnoDB and MyISAM are so far apart in terms of time to complete a full table scan? Am I just missing something obvious in the MySQL config?  I've used MySQL for years and have had no problems with it as long as my problems were constrained to the set of "indexes can solve this" problems.</p>
  
  <p>Partitioning will obviously also solve this problem, but at a much higher
  cost.</p>
  
  <p>For reference, here are my config files for MySQL and PGSQL:</p>
  
  <h2>MYSQL CONFIG</h2>
  
  <pre><code>[client]
  port=3306
  
  [mysql]
  default-character-set=utf8
  
  [mysqld]
  port=3306
  basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
  datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
  default-character-set=utf8
  default-storage-engine=INNODB
  log="c:/logs/mysql/mysqld.log"
  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  max_connections=700
  query_cache_size=0M
  table_cache=1400
  tmp_table_size=16M
  thread_cache_size=34
  
  myisam_max_sort_file_size=100G
  myisam_sort_buffer_size=8M
  key_buffer_size=200M
  read_buffer_size=64K
  read_rnd_buffer_size=256K
  sort_buffer_size=208K
  
  innodb_additional_mem_pool_size=2M
  innodb_flush_log_at_trx_commit=1
  innodb_log_buffer_size=1M
  innodb_buffer_pool_size=200M
  innodb_log_file_size=18M
  innodb_thread_concurrency=10
  </code></pre>
  
  <h2>PGSQL CONFIG</h2>
  
  <pre><code>listen_addresses = '*'        # what IP address(es) to listen on;
                  # comma-separated list of addresses;
                  # defaults to 'localhost', '*' = all
                  # (change requires restart)
  port = 5432                # (change requires restart)
  max_connections = 100            # (change requires restart)
  
  shared_buffers = 32MB            # min 128kB
                  # (change requires restart)
  temp_buffers = 12MB            # min 800kB
  maintenance_work_mem = 32MB        # min 1MB
  
  log_destination = 'stderr'        # Valid values are combinations of
                  # stderr, csvlog, syslog and eventlog,
                  # depending on platform.  csvlog
                  # requires logging_collector to be on.
  
  logging_collector = on        # Enable capturing of stderr and csvlog
                  # into log files. Required to be on for
                  # csvlogs.
                  # (change requires restart)
  
  log_line_prefix = '%t'            # special values:
                  #   %u = user name
                  #   %d = database name
                  #   %r = remote host and port
                  #   %h = remote host
                  #   %p = process ID
                  #   %t = timestamp without milliseconds
                  #   %m = timestamp with milliseconds
                  #   %i = command tag
                  #   %c = session ID
                  #   %l = session line number
                  #   %s = session start timestamp
                  #   %v = virtual transaction ID
                  #   %x = transaction ID (0 if none)
                  #   %q = stop here in non-session
                  #        processes
                  #   %% = '%'
                  # e.g. '&lt;%u%%%d&gt; '
  
  datestyle = 'iso, mdy'
  lc_messages = 'English_United States.1252'            # locale for system error message
                  # strings
  lc_monetary = 'English_United States.1252'            # locale for monetary formatting
  lc_numeric = 'English_United States.1252'            # locale for number formatting
  lc_time = 'English_United States.1252'                # locale for time formatting
  
  default_text_search_config = 'pg_catalog.english'
  </code></pre>
  
  <p>Also to understand how large these data sets actually are in MySQL, here's a 
  show table status \G on them, in case this helps:</p>
  
  <pre><code>*************************** 1. row ***************************
             Name: sample_innodb
           Engine: InnoDB
          Version: 10
       Row_format: Compact
             Rows: 5000205
   Avg_row_length: 100
      Data_length: 500154368
  Max_data_length: 0
     Index_length: 149700608
        Data_free: 0
   Auto_increment: NULL
      Create_time: 2010-02-02 17:27:50
      Update_time: NULL
       Check_time: NULL
        Collation: utf8_general_ci
         Checksum: NULL
   Create_options:
          Comment: InnoDB free: 497664 kB
  
  *************************** 2. row ***************************
             Name: sample_isam
           Engine: MyISAM
          Version: 10
       Row_format: Dynamic
             Rows: 5000000
   Avg_row_length: 72
      Data_length: 360006508
  Max_data_length: 281474976710655
     Index_length: 1024
        Data_free: 0
   Auto_increment: NULL
      Create_time: 2010-02-02 17:27:50
      Update_time: 2010-02-02 17:37:23
       Check_time: NULL
        Collation: utf8_general_ci
         Checksum: NULL
   Create_options:
          Comment:
  </code></pre>