Need help optimizing slow SQL queries

jmarfurt · #1 (**permalink**) 08-06-07, 10:46

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!):

Quote:

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.

gvee · #2 (**permalink**) 08-06-07, 10:56

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

aschk · #3 (**permalink**) 08-06-07, 12:20

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech