title: Very slow innodb operations
body: <p>I have php script for comparing url to our db urls and saving only new ones. The problem is that each step of this script is taking very long (more than ~10 minutes total). Here's the steps: </p>
  
  <pre><code>$query = sprintf("DELETE FROM %s ; ", $DB ['compare_result_table']);
  
  $query = sprintf("LOAD DATA INFILE '%s' IGNORE INTO TABLE %s
          FIELDS TERMINATED BY ','
          ENCLOSED BY \"'\"
          LINES TERMINATED BY '\n'
          (domain, url_value)",
        $real_out_path, $DB["compare_result_table"]);
  
  $query = sprintf(" INSERT INTO %s SELECT domain 
          FROM %s ;", $DB ['compare_url_table'], 'domain_data' );
  
  $query = sprintf("DELETE a.*
                FROM %s a INNER JOIN %s b using (domain);",
           $DB["compare_result_table"], $DB ['compare_url_table']);
  </code></pre>
  
  <p>Here's the tables description:</p>
  
  <pre><code>DROP TABLE IF EXISTS cmp_url;
  CREATE TABLE cmp_url
  (
      domain varchar(255),
      index (domain)
  );
  
  DROP TABLE IF EXISTS cmp_result;
  CREATE TABLE cmp_result
  (
      domain varchar(255),
      url_value varchar(4096),
      index (domain)
  
  );
  </code></pre>
  
  <p>Everytime I call show PROCESSLIST I see that every step (excluding, maybe data load from file) is taking very long. The domain_data table, that contains our domains to compare is 600 000 records now. The bigger it becomes, the slower are all the steps. </p>
  
  <p>Here's some options I changed in my.cnf:</p>
  
  <pre><code>innodb_lock_wait_timeout = 360
  innodb_autoinc_lock_mode=2
  binlog_format=mixed
  transaction-isolation = READ-COMMITTED
  </code></pre>
  
  <p>But it doesnt change anything. How can I solve that issue? Thank you for your time.</p>