title: Optimize mysql query in python
body: <p>I am connecting to a mysql db with mysql-connector in python. In the normal run of events, I won't have to process more than 1000 rows but occasionally I might need to process 20k plus rows of data and that is where my query gets very slow and times out. I have tried using an iterator in mysql-connector so as to limit the amount I process at a given time but it seems this is only implemented for MySQLdb in py.</p>
  
  <pre><code>SELECT
          ROUND(311.30004 - (20.110938 * temp.value) 
          - (2.012626 * hum.value) - (2.006346 * airspeed.value) 
          + (0.4059 * POWER(temp.value, 2) + (0.006604 * POWER(hum.value, 2)) 
          + (3.151145 * POWER(airspeed.value, 2) + (0.05555 * temp.value * hum.value) 
          - (0.37037 * temp.value * airspeed.value) + (0.03968 * hum.value * airspeed.value))), 2) 
          AS value, temp.time, temp.x, temp.y, temp.z, temp.round_id, 'predicted_frequency', temp.round_number, temp.day_of_production
  FROM round_data_temperature AS temp 
      INNER JOIN round_data_humidity AS hum 
          ON temp.round_number = hum.round_number 
             AND temp.x = hum.x 
             AND temp.y = hum.y AND DATE_FORMAT(temp.time, '%Y-%m-%d %H:%i:00') = DATE_FORMAT(hum.time, '%Y-%m-%d %H:%i:00')
      INNER JOIN round_data_airspeed AS airspeed 
          ON temp.round_number = airspeed.round_number 
              AND temp.x = airspeed.x AND temp.y = airspeed.y 
              AND DATE_FORMAT(temp.time, '%Y-%m-%d %H:%i:00') = DATE_FORMAT(airspeed.time, '%Y-%m-%d %H:%i:00')
  WHERE temp.round_id ='xxxxx'
  </code></pre>
  
  <p>This code runs very fast on just mysql but is very slow and times out in mysql-connector in python with 20k plus rows so I decided to insert immediately to avoid selecting very large packets of data in python. I did the below:</p>
  
  <pre><code>INSERT INTO round_data_pf (value, time, x, y, z, round_id, observable_name, round_number, day_of_production)
  SELECT
          ROUND(311.30004 - (20.110938 * temp.value) 
          - (2.012626 * hum.value) - (2.006346 * airspeed.value) 
          + (0.4059 * POWER(temp.value, 2) + (0.006604 * POWER(hum.value, 2)) 
          + (3.151145 * POWER(airspeed.value, 2) + (0.05555 * temp.value * hum.value) 
          - (0.37037 * temp.value * airspeed.value) + (0.03968 * hum.value * airspeed.value))), 2) 
          AS value, temp.time, temp.x, temp.y, temp.z, temp.round_id, 'pf', temp.round_number, temp.day_of_production
  FROM round_data_temperature AS temp 
      INNER JOIN round_data_humidity AS hum 
          ON temp.round_number = hum.round_number 
              AND temp.x = hum.x 
              AND temp.y = hum.y 
              AND DATE_FORMAT(temp.time, '%Y-%m-%d %H:%i:00') = DATE_FORMAT(hum.time, '%Y-%m-%d %H:%i:00')
      INNER JOIN round_data_airspeed AS airspeed 
          ON temp.round_number = airspeed.round_number 
              AND temp.x = airspeed.x 
              AND temp.y = airspeed.y 
              AND DATE_FORMAT(temp.time, '%Y-%m-%d %H:%i:00') = DATE_FORMAT(airspeed.time, '%Y-%m-%d %H:%i:00')
  WHERE temp.round_id ='xxxxx'
  </code></pre>
  
  <p>This code times out in both mysql and mysql-connector in python when I have about 20k rows of data.</p>
  
  <p>I am looking to optimize the code so it selects and inserts faster when I have say 20k plus rows.</p>