#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2005
    Posts
    4
    Rep Power
    0

    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
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    You would need to show us the table creation statements and the actual query you are using.

    26 million rows isn't all that large

    run an explain on the query as well to see what indexes are being used.
  4. #3
  5. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Do you have all 26 M rows in your development machine?

    If you do then compare the execution plan between your development machine and your production server to see if there is a difference in execution plan.

    (Sniped by Guelphdad )
    /Stefan
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279

IMN logo majestic logo threadwatch logo seochat tools logo