I have a query. On my local Windows 7 machine it takes 1.31 seconds to return the result. In the production database it takes 6 minutes and 15 seconds until it returns. The MySQL version on my local PC is 5.5.20 64-bit. In the production database the MySQL version is "mysql Ver 14.14 Distrib 5.1.55, for portbld-freebsd8.1 (amd64) using 5.2". That table is over 1.6 GB and over 26 million rows. Were using the InnoDB storage engine.
Have we exceeded some limit? Will deleting old rows in that table help? We don't want to loose old statistics unless we absolutely have to.
You are on a later version on your local PC 5.5.20 whereas your production environment is on 5.1.55. There might be some performance benefits of using 5.5 over 5.1.
I would check several things:
1. Check that the execution plan on your local machine matches that of the production. I suspect that it is using indexes that do not exist or the incorrect ones.
2. Check that the indexes on the production database match those on your local database.
3. If there have been lots of inserts/deletes on production in the various tables reorganize these.
4. Analyze the statistics on each of the tables to make sure that the execution plans are using the latest statistics.