Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Canada
    Posts
    239

    Much longer execution time.... same code

    I just moved my site to a different dedicated box.... everything works fine, except for 2 pages, which use a join to compare 2 tables. the code is as follows:
    PHP Code:
    $query "SELECT * from links1, phpbb_users WHERE links1.username = phpbb_users.username AND status = 0 AND $link_types ORDER BY posttime"
    On my old box... he page that has this code took 0.003s to execute... . On my new box, it takes 6.3s.

    If I change the code to this, it goes down to 0.003 again:
    PHP Code:
    $query "SELECT * from links1 WHERE  status = 0 AND $link_types ORDER BY posttime"
    but then my script is broken.

    The only different between the 2 servers is version of php/mysql. Old was running php4/mysql4 new is running php5/mysql5.

    Could the versions of php/mysql be responsible for this, or is this something different?

  2. #2
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,416
    First off: check for indexes. Compare the indexes built on both the old and new databases -- chances are high the new box is missing an index on phpbb_users or on links1; look especially at the username column in both.

    (This assumes you have significant data, not just a few dozens or hundreds of rows)
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  3. #3
    Join Date
    Oct 2002
    Location
    Canada
    Posts
    239
    Huzza! I made username field unique, and it started working!

    Thanks!

  4. #4
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,416
    There you go. Its surprising how many software packages create data without creating indexes; sometimes backup and restore are to blame. Whenever performance in a db app seems an issue, check indexes first.

    Another tip - use your db's "explain" facility - it'll show you the query plan for a given query and sometimes uncover things not immediately visible.
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  5. #5
    Join Date
    Oct 2002
    Location
    Canada
    Posts
    239
    Is there a guide to indexes somewhere? Im starting to do my own coding work, and I dont totally understand them.

Posting Permissions

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