If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > MySQL > MySQL query takes long time to return

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Nov 2011
Posts: 2
MySQL query takes long time to return

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.
Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 860
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.
Ronan Cashell
Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: Oct 2009
Location: 221B Baker St.
Posts: 487
How many concurrent users are active on both systems?

Are there other queries that experience a similar slowdown or only this particular query?
Reply With Quote
  #4 (permalink)  
SQL Consultant
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On