Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Unanswered: Need help optimizing slow SQL queries

    Hi, I have absolutely no knowledge of PHP or MySQL .... I moderate a PHPBB forum at www.savingshelterpets.com
    Our web host (SiteGround) has taken our site down temporarily because we are overloading the server. I have no idea how to fix the problem, so hopefully someone here can help me out! Smiley

    PHP version 4.4.4
    MySQL version 5.0.27-standard-log

    Here's the info sent to me by SiteGround (I don't understand a word of it!):

    Upon further investigation, it turned out that the following queries in your account are slow and heavily consume server resources:

    # User@Host: savingsh_phpb1[savingsh_phpb1] @ localhost []
    # Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 1284
    use savingsh_phpbb2;
    SELECT user_id, username, user_password, user_active, user_level, user_login_tries, user_last_login_try
    FROM phpbb_users
    --
    delete from rs_stat_ip where platnost_do<'2007-08-03 16:49:43';
    # User@Host: savingsh_phpb1[savingsh_phpb1] @ localhost []
    # Query_time: 5 Lock_time: 3 Rows_sent: 1 Rows_examined: 0
    use savingsh_phpbb2;
    SELECT * FROM phpbb_optimize_db;
    # User@Host: binaryte_lhlp1[binaryte_lhlp1] @ localhost []
    --
    # Time: 070803 16:50:27
    # User@Host: savingsh_phpb1[savingsh_phpb1] @ localhost []
    # Query_time: 4 Lock_time: 2 Rows_sent: 1 Rows_examined: 0
    use savingsh_phpbb2;
    SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments
    FROM phpbb_topics t, phpbb_forums f

    In order to have the limitations removed, please optimize your script.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT user_id, username, user_password, user_active, user_level, user_login_tries, user_last_login_try
    FROM phpbb_users
    You have 1284 users - you are selecting all of them
    Code:
    delete from rs_stat_ip where platnost_do<'2007-08-03 16:49:43'
    Delete all records from "rs_stat_ip" table where the field "platnost_do" is less than 03 August 2007. Looks like it didn't actually delete anything "Rows examined: 0"
    Code:
    SELECT * FROM phpbb_optimize_db
    I imagine that returns a heckofalot (phpbb_optimize_db table I'm guessing is pretty big too)
    Code:
    SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments
    FROM phpbb_topics t, phpbb_forums f
    That's a rubbish, full joined query and will need some good optimization. If you're picking data from two tables, generally you want to join the two to find related records.

    More on JOINs can be found here: http://w3schools.com/sql/sql_join.asp
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Two out of those procedures examine 0 results. Not good! No point in running them if they don't do anything...
    Also, it sounds like they might be iterating through every single record to finish.
    Without seeing the indexes it's going to be hard to guess why these queries are actually taking time. We can guess and say there are a lot of records in the DB.

    Do run administration functions regularly on the forum? Looks like query 2 & 3 might be admin facilities.

    I'm curious to know when these functions get run as they seem particularly poor, and I can't imagine that EVERY record is brought back for listing of topics/forums. PhpBB isn't that silly... is it?

Posting Permissions

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