title: Significant performance difference between a local mysql server and a production mysql server
body: <p>I am the new maintainer of an application running in production MySQL. The previous maintainer has gone leaving few documentation, and is not contactable.</p>
  
  <p>The  issue I face is that the following request takes around 10 seconds to execute:</p>
  
  <pre><code>SELECT COUNT(*) FROM `users` WHERE (`active` = TRUE AND `deleted_at` IS NULL);
  </code></pre>
  
  <p>There are about 170,000 records in the users table and no indexes.</p>
  
  <p>The EXPLAIN command on this request:</p>
  
  <pre><code>mysql&gt; EXPLAIN SELECT COUNT(*) FROM `users` WHERE (`active` = TRUE AND `deleted_at` IS NULL);
  +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
  +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
  |  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 145407 | Using where |
  +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
  </code></pre>
  
  <p>Current site owner (non technical person) told me that performances dropped suddenly 2 weeks ago. So before trying to add indexes, I have dumped the production db into my local MySQL server. The e same requests takes only 4 seconds locally.</p>
  
  <p>I'm quite surprised by this difference, and I'm wondering if it's possible that some issue at the OS or MySQL server might explain it? Is it worth investigating in this way? If so how? If not, are such performance "normal" for such a query without indexes (I have limited experienced about SQL performance)?</p>
  
  <p>On the server:</p>
  
  <pre><code>$ mysql -u root --version
  mysql  Ver 14.14 Distrib 5.1.44, for unknown-linux-gnu (x86_64) using readline 5.1
  $ cat /etc/redhat-release 
  CentOS release 5.4 (Final)
  </code></pre>
  
  <p>Local machine:</p>
  
  <pre><code>$ $ mysql -u root --version
  mysql  Ver 14.14 Distrib 5.1.42, for apple-darwin10.2.0 (i386) using  EditLine wrapper
  </code></pre>
  
  <p>Thanks!</p>
  
  <p>EDIT for Traroth: </p>
  
  <ul>
  <li>RAM: 4GB on production, 2GB on local machine</li>
  <li>request caching: indeed on local machine subsequent requests are much more quicker, on production it's getting better also but still too slow (around 5 seconds in best case). Do you think that's normal?</li>
  <li>both DB use InnoDB</li>
  <li>filesystem: production box is a VPS, 'simfs' shows up when I look up for the filesystem</li>
  </ul>