Need help optimizing slow SQL queries (?)

[jmarfurt]

Hi, I have absolutely  no knowledge of PHP or SQL  .... 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!

 

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.

[fenway]

First query -- you're getting all the users.

Second query -- another query with no where clause.

Third query -- I don't see a join condition. 

 

Then again, maybe they're not sending you the whole log.

[mrjcfreak]

As well as checking the efficiency of code check the usage of it.

It's probably a good idea too to pipe clean all your forum stats, logs, dead/spam users. If you have a spam bot hammering your forum trying to register all the time then try adding a human check if you don't already have one. Delete old unused accounts, make shorter posts per page views etc.