title: Why postgresql's single insert statement is faster than mysql?
body: <p>I wonder why postgresql's single "insert" statement is completely faster than MySQL's when autocommit is turned on?  The following is the same code that I did on them.</p>
  
  <p>Version:</p>
  
  <pre><code>MySQL: 5.6.10 
  PostgreSQL:  PostgreSQL 9.3.2 on x86_64
  </code></pre>
  
  <p>Table definition:</p>
  
  <p>MySQL:</p>
  
  <pre><code>CREATE TABLE `user` (
    `username` char(36) NOT NULL,
    `password` char(32) NOT NULL,
    `register_time` datetime NOT NULL,
    `mobile_phone` char(11) NOT NULL,
    `is_admin` enum('yes','no') NOT NULL,
    PRIMARY KEY (`username`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>PostgreSQL:</p>
  
  <pre><code>CREATE TYPE ytt_enum AS ENUM ('yes','no');
  CREATE TABLE ytt."user" (
    "username" char(36) NOT NULL,
    "password" char(32) NOT NULL,
    "register_time" timestamp  NOT NULL,
    "mobile_phone" char(11) NOT NULL,
    "is_admin" ytt_enum NOT NULL,
    PRIMARY KEY ("username")
  ) ;
  </code></pre>
  
  <p>Store functions:</p>
  
  <p>MySQL:</p>
  
  <pre><code>DELIMITER $$
  
  USE `t_girl`$$
  
  DROP PROCEDURE IF EXISTS `sp_insert_user_simple`$$
  
  CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_user_simple`(
  IN f_input      INT
  )
  BEGIN
      DECLARE i      INT DEFAULT 0;
      WHILE i &lt;= f_input
      DO
          INSERT INTO t_girl.user (`username`, `password`, register_time,mobile_phone,is_admin) 
          VALUES (UUID(),MD5(REPLACE(UUID(),'-','')),DATE_SUB(NOW(),INTERVAL CEIL(RAND()*40)  DAY),CEIL(RAND()*10000)+13800000000,IF(TRUNCATE(RAND()*2,0)=1,'yes','no'));
          SET i = i + 1;
      END WHILE;
  END$$
  
  DELIMITER ;
  </code></pre>
  
  <p>PostgreSQL:</p>
  
  <pre><code>CREATE  or replace function sp_insert_user_simple(
  IN f_input      INT
  ) returns void as
  $ytt$
      declare i int := 0;
      v_username char(36);
      v_password char(32);
      v_register_time timestamp;
      v_mobile_phone char(11);
      v_is_admin ytt_enum;
  BEGIN
  
      WHILE i &lt; f_input
      loop
          v_username := uuid_generate_v1();
          v_password :=MD5(REPLACE(uuid_generate_v1()::text,'-',''));
          v_register_time := to_timestamp((now() - '1 day'::interval*ceil(random()*40))::text,'yyyy-mm-dd HH24:MI:SS');
          v_mobile_phone :=CEIL(RANDOM()*10000)+13800000000;
          v_is_admin := (case TRUNC(RANDOM()*2) when 1  then 'yes' else'no' end)::ytt_enum;
          INSERT INTO ytt.user (username, password, register_time,mobile_phone,is_admin) 
          VALUES (v_username,v_password,v_register_time,v_mobile_phone,v_is_admin);
          i := i + 1;
      END loop;
  END;
  $ytt$language plpgsql;
  </code></pre>
  
  <p>Parameters:</p>
  
  <pre><code>MySQL:
  innodb_buffer_pool_size=32M
  bulk_insert_buffer_size=20M
  autocommit=on
  PostgreSQL:
  shared_memory=32M
  effective_cache_size=20M
  autocommit=on
  </code></pre>
  
  <p>Test result:
  MySQL:</p>
  
  <pre><code>mysql&gt; call sp_insert_user_simple(10000);
  Query OK, 1 row affected (1 min 9.93 sec)
  </code></pre>
  
  <p>PostgreSQL:</p>
  
  <pre><code>ytt=# select sp_insert_user_simple(10000); 
   sp_insert_user_simple 
  -----------------------
  
  (1 row)
  
  Time: 1177.043 ms
  </code></pre>
  
  <p>The above test shows that MySQL's running time is 69.93 second but PostgreSQL's is only 1.17 second.<br>
  Any answer is appreciated. Thanks.</p>