title: MySQL poor performance
body: <p>I have a one huge issue with one portion of my app. I am using SQLAlchemy and MySQL combination, and most of the things work fine, but there is one itch that keep loading forever, sometimes even 5-6 minutes, loading of the customer list. That table has around 3000 rows, which should be fairly small for database standards, and I have a simple join on a slightly larger table (25k rows).</p>
  
  <p>The query in SQL Alchemy is as follows:</p>
  
  <pre><code>last_inv = db.session.query(Sales.id).order_by(Sales.invoice_date.desc()).filter(Customer.email == Sales.email).limit(1).correlate(Customer)
  results = db.session.query(Customer, last_inv.as_scalar()).filter_by(archive=0)
  </code></pre>
  
  <p>The raw SQL looks like this:</p>
  
  <pre><code>SELECT customer.id AS customer_id
       , customer.first_name AS customer_first_name
       , customer.middle_name AS customer_middle_name
       , customer.last_name AS customer_last_name
       , customer.email AS customer_email
       , customer.password AS customer_password
       , customer.address1 AS customer_address1
       , customer.address2 AS customer_address2
       , customer.city AS customer_city
       , customer.state AS customer_state
       , customer.zip AS customer_zip
       , customer.country AS customer_country
       , customer.phone AS customer_phone
       , customer.cell_phone AS customer_cell_phone
       , customer.current_plan AS customer_current_plan
       , customer.minutes_current_plan AS customer_minutes_current_plan
       , customer.orig_sales_id AS customer_orig_sales_id
       , customer.sales_id AS customer_sales_id
       , customer.team_id AS customer_team_id
       , customer.refill_date AS customer_refill_date
       , customer.minutes_refill_date AS customer_minutes_refill_date
       , customer.active AS customer_active
       , customer.archive AS customer_archive
       , customer.imported AS customer_imported
       , customer.ipaddress AS customer_ipaddress
       , customer.auto_renewal AS customer_auto_renewal
       , customer.signup_date AS customer_signup_date
       , customer.esn AS customer_esn
       , customer.last_update_date AS customer_last_update_date
       , customer.last_update_by AS customer_last_update_by
       , customer.notes AS customer_notes
       , customer.current_pin AS customer_current_pin
       , customer.minutes_current_pin AS customer_minutes_current_pin
       , customer.security_pin AS customer_security_pin
       , (SELECT sales.id  
            FROM sales 
           WHERE customer.email = sales.email 
           ORDER 
              BY sales.invoice_date DESC LIMIT 1) AS anon_1 
    FROM customer 
   WHERE customer.team_id = 1 
     AND customer.archive = 0
  </code></pre>
  
  <p>I have tried numerous things, but this is really starting to make me feel hopeless. This is all running on an Amazon and <code>htop</code> shows 100% usage for mysql when this is run. Profiler of a query on phpmyadmin, HeidiSQL show that is is done in less than two seconds (when not being hit in cahce), so its not the actual query causing this (as fair as I comprehend this).</p>
  
  <p>This is what <code>EXPLAIN</code> shows:</p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   PRIMARY customer    ALL NULL    NULL    NULL    NULL    3621    Using where
  2   DEPENDENT SUBQUERY  sales   ALL NULL    NULL    NULL    NULL    22619   Using where;    Using filesort
  </code></pre>
  
  <p>Profiler from phpmyadmin is <a href="http://pastebin.com/RbA4Hcjn" rel="nofollow noreferrer">here</a> and visual representation <a href="http://pokit.org/get/img/50d27fbf709eb4cf7793a52e03a6b789.jpg" rel="nofollow noreferrer">here</a>.</p>
  
  <p>I am running a m1.small instance on EC2, with 1650MB of RAM.</p>
  
  <p>I have run a mysqlprofiler as well, and here are the results <a href="http://pastebin.com/C4UtmbEd" rel="nofollow noreferrer">before</a> and <a href="http://pastebin.com/FKaF5ibf" rel="nofollow noreferrer">after</a> the optimizations I have done. My <code>my.cnf</code> file is <a href="http://pastebin.com/CtbwgGpV" rel="nofollow noreferrer">here</a>.</p>
  
  <p>I have tried running <code>OPTIMIZE</code> on tables, but for some reason the number of unoptimized tables is always 98, so I guess I am doing something wrong. I used <a href="http://pastebin.com/GYc50LH1" rel="nofollow noreferrer">this</a> script for that, as well as raw sql in phpmyadmin, without success.</p>