Rick, thanks for all the suggestions. I will try each one.
I understand what I provided was not full information regarding cache. I read up on that some more in the past few days. We are running with the INNODB engine.
Adding to my problems attempting to "optimize" the queries I fell into, is this gem. For a while I have known that the testing environment and production environment are not the same. Not until yesterday did I finally get our admin (outside contractor) to fill me in on the details:
Quote
Jeff Nolan
One is mysql and the other is XtraDB Cluster. Very different, but both use Percona MySQL at the core. Also, some differing runtime configurations such as memory allocated to innodb, etc.
The staging server is a single instance VPS running on top of QEMU. The XtraDB Cluster is made up of two VPS's running on top of XEN.
I'm not convinced he knows what to look for in the production set up. I have a query that runs in 26 seconds in the staging environment but takes over 3 minutes in production!! It is a query joining 3 tables. Comparing those tables in the 2 environments shows identical DDL and row counts within a few thousand.
Do you have any suggestions on what I should ask him to look at with regards to memory, buffer pool etc. in the production environment?