title: MySQL 10x slower on one server compared to another
body: <p>I have a live server and my dev server, and I am finding that queries on my <em>LIVE</em> (not dev) server run 10x slower, even though the live server is more powerful and they are both running comparable load. It's not a database structure thing because I load the backup from the live server into my dev server. </p>
  
  <p>Does anybody have any ideas on where I could look for the discrepancy? Could it be a MySQL config thing? Where should I start looking?  </p>
  
  <p>Live Server:</p>
  
  <pre>
  mysql> SELECT count(`Transaction`.`id`) as count, sum(`Transaction`.`amount`) as sum, sum(Transaction.citiq_margin+rounding + Transaction.citiq_margin_vat) as revenue FROM `transactions` AS `Transaction` LEFT JOIN `meters` AS `Meter` ON (`Transaction`.`meter_id` = `Meter`.`id`) LEFT JOIN `units` AS `Unit` ON (`Meter`.`unit_id` = `Unit`.`id`) WHERE (NOT (`Unit`.`building_id` IN ('1', '85')) AND NOT (`Transaction`.`state` >= 90)) AND DAY(`Transaction`.`created`) = DAY(NOW()) AND YEAR(`Transaction`.`created`) = YEAR(NOW()) AND (MONTH(`Transaction`.`created`)) = MONTH(NOW());
  +-------+---------+---------+
  | count | sum     | revenue |
  +-------+---------+---------+
  |   413 | 3638550 |  409210 |
  +-------+---------+---------+
  1 row in set (2.62 sec)
  
  [root@mises ~]# uptime
   17:11:57 up 55 days, 1 min,  1 user,  load average: 0.45, 0.56, 0.60
  </pre>
  
  <p>Dev Server (result count is different because of slight time delay from backup):</p>
  
  <pre>
  mysql>  SELECT count(`Transaction`.`id`) as count, sum(`Transaction`.`amount`) as sum, sum(Transaction.citiq_margin+rounding + Transaction.citiq_margin_vat) as revenue FROM `transactions` AS `Transaction` LEFT JOIN `meters` AS `Meter` ON (`Transaction`.`meter_id` = `Meter`.`id`) LEFT JOIN `units` AS `Unit` ON (`Meter`.`unit_id` = `Unit`.`id`) WHERE (NOT (`Unit`.`building_id` IN ('1', '85')) AND NOT (`Transaction`.`state` >= 90)) AND DAY(`Transaction`.`created`) = DAY(NOW()) AND YEAR(`Transaction`.`created`) = YEAR(NOW()) AND (MONTH(`Transaction`.`created`)) = MONTH(NOW());
  +-------+---------+---------+
  | count | sum     | revenue |
  +-------+---------+---------+
  |   357 | 3005550 |  338306 |
  +-------+---------+---------+
  1 row in set (0.22 sec)
  
  [www@smith test]$ uptime
   18:11:53 up 12 days,  1:57,  4 users,  load average: 0.91, 0.75, 0.62
  
  </pre>
  
  <p>Live Server (2 x Xeon Quadcore):</p>
  
  <pre>
  processor       : 7
  vendor_id       : GenuineIntel
  cpu family      : 6
  model           : 44
  model name      : Intel(R) Xeon(R) CPU           E5620  @ 2.40GHz
  stepping        : 2
  cpu MHz         : 2395.000
  cache size      : 12288 KB
  physical id     : 0
  siblings        : 8
  core id         : 10
  cpu cores       : 4
  </pre>
  
  <p>Dev Server (1 x Quadcore)</p>
  
  <pre>
  processor       : 3
  vendor_id       : GenuineIntel
  cpu family      : 6
  model           : 23
  model name      : Intel(R) Core(TM)2 Quad CPU    Q8300  @ 2.50GHz
  stepping        : 10
  microcode       : 0xa07
  cpu MHz         : 1998.000
  cache size      : 2048 KB
  physical id     : 0
  siblings        : 4
  core id         : 3
  cpu cores       : 4
  </pre>
  
  <p>Live Server: </p>
  
  <ol>
  <li>CentOS 5.7</li>
  <li>MySQL ver 5.0.95</li>
  </ol>
  
  <p>Dev Server: </p>
  
  <ol>
  <li>ArchLinux </li>
  <li>MySQL ver 5.5.25a</li>
  </ol>