Results 1 to 4 of 4
  1. #1
    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.

  2. #2
    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)
    http://www.it-iss.com

  3. #3
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    How many concurrent users are active on both systems?

    Are there other queries that experience a similar slowdown or only this particular query?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •