[2.0.x] Tweaks for large forums

The 2.0.x discussion forum has been locked; this will remain read-only. The 3.0.x discussion forum has been renamed phpBB Discussion.
Locked
davidh44
Registered User
Posts: 386
Joined: Sat Mar 09, 2002 5:56 am

Post by davidh44 »

BondGamer wrote:
davidh44 wrote: Do you happen to have instructions on how to keyword only the subject lines? That's something I'd really like to do (in lieu of getting rid of search completely).

I found some mods that allow you to search subject only, but that's only on the user end (everything still get indexed for search).

I am not sure and have not used this, but I think it would go like this.

Open the includes/functions_search.php file. Search for this line in the add_search_words function:

Code: Select all

$search_raw_words['text'] = split_words(clean_words('post', $post_text, $stopword_array, $synonym_array));
Replace with:

Code: Select all

//$search_raw_words['text'] = split_words(clean_words('post', $post_text, $stopword_array, $synonym_array));
That should disable indexing of words in posts but not titles.

Thanks for the suggestion! Can any coders out there confirm that this works?
BlackHoleMars
Registered User
Posts: 20
Joined: Sun Dec 04, 2005 11:12 am
Contact:

Post by BlackHoleMars »

well, yes because you are simply commenting out the line that loads up the 'text' field in the search results array. It should work.
Get Some Games Game reviews
smurfix
Registered User
Posts: 1
Joined: Sun Dec 11, 2005 5:39 pm

Kill

Post by smurfix »

I don't know whether this has been mentioned yet, because I got lost scanning through 34 pagesof random hints ;-) but even if you don't heavily optimize searches there's something you can do about speeding them up.

Consider the code with $delete_search_ids in search.php. Examine what it does. Yes, it creates a DELETE ... NOT IN query. Except that the part after the NOT IN has every session ID in existence in it. That's a bit excessive -- 3000 sessions (not that much when people tend to stay logged in) equals one 110-KByte query.

Sorry, but that's stupid. Throw out this code now, and replace it with one table change

Code: Select all

 alter table SEARCH_TABLE add timestamp timestamp, add index ts(timestamp);
and one cronjob

Code: Select all

delete from SEARCH_TABLE where timestamp < date_sub(now(), interval 30 minute);
or whatever. Problem solved.

If you want to get even more fancy, you can use a multi-table statement:

Code: Select all

delete SEARCH_TABLE from SEARCH_TABLE left join SESSIONS_TABLE on SEARCH_TABLE.session_id = SESSIONS_TABLE.session_id where SESSIONS_TABLE.session_id is null;
You don't need the timestamp column with this solution, but this way will be slower.

If you want to get even more fancy, then forget about all of this, move your tables to InnoDB, and add a FOREIGN KEY between the two, with ON DELETE CASCADE.
User avatar
EverettB
Registered User
Posts: 326
Joined: Fri Aug 01, 2003 7:11 pm
Location: North America

Post by EverettB »

While I agree with your assessment of the current design, the delete query you mentioned takes 0.015 seconds on my database to run with 2700 sessions.

I wouldn't change the sessions table to InnoDB from HEAP.
User avatar
EverettB
Registered User
Posts: 326
Joined: Fri Aug 01, 2003 7:11 pm
Location: North America

Post by EverettB »

nl2dav wrote: I also read that some of you removed some or all of the quite static queries....

Code: Select all

SELECT config_name, config_value FROM phpbb_config
SELECT * FROM phpbb_themes WHERE themes_id = 1
SELECT * FROM phpbb_ranks ORDER BY rank_special, rank_min
SELECT * FROM phpbb_forums ORDER BY cat_id, forum_order
SELECT * FROM phpbb_smilies
.... and put in a static PHP file... I understand the logic and I could implement that to my site but I calculated the time it would take to get that information from the database and I wonder if it would decrease if I put this information in a file.

It needs time to read from the disk also, so is it really that much faster than getting the values and settings from the database?


I have implemented a number of these static file improvements and the overall load dropped quite a bit.
capleton
Registered User
Posts: 20
Joined: Fri Sep 03, 2004 10:17 am

Post by capleton »

Hi, this isn't exactly a tweak but thought i ask here since you guys seems to be experts in SQL.
I've done a script that checks if a username is in a db and if it is setting a variable to contain an img, else set an empty var, if there's a better way to do this query or better yet, implement it at some other place in viewtopic.php.
Problem is it set's the var for some users that shouldn't have the var set, im gonna post the code and see if you can spot something wrong with it.

Code: Select all

$sql = "SELECT *
   FROM phpbb_column
   WHERE username = '$poster'";
if ( !$db->sql_query($sql) )
{
   message_die(GENERAL_ERROR, "Could not fetch lövenbyggare.", '', __LINE__, __FILE__, $sql);
}
if ($result = $db->sql_query($sql)) {
   while($row = $db->sql_fetchrowset($result)) {
      $variable = '<img src="http://url" alt="img">';
   }
}
else {
   $variable = '';
}
$db->sql_freeresult($result);

Thankful if someone could help out!
DeWol
Registered User
Posts: 2
Joined: Sun Dec 18, 2005 4:43 am

Post by DeWol »

Hi, i used the viewforum.php twaek and its really amazing. First of all, thanks.

However, now i cant see the avatars (the img under the nick). Can anyone tell me why?.

Im kind of new with php and sql and i dont have any clue... :oops:

I hope you can understand me because english is not my language.. :?
DeWol
Registered User
Posts: 2
Joined: Sun Dec 18, 2005 4:43 am

Post by DeWol »

DeWol wrote: Hi, i used the viewforum.php twaek and its really amazing. First of all, thanks.

However, now i cant see the avatars (the img under the nick). Can anyone tell me why?.

Im kind of new with php and sql and i dont have any clue... :oops:

I hope you can understand me because english is not my language.. :?


I figured out how to fix it.

thanks anyway..

:wink:
lanzer
Registered User
Posts: 152
Joined: Wed Oct 10, 2001 10:00 am
Contact:

Post by lanzer »

capleton wrote: Hi, this isn't exactly a tweak but thought i ask here since you guys seems to be experts in SQL.
...
Thankful if someone could help out!


Hi Capleton, to avoid going off-topic, I've replied on another thread:

http://www.phpbb.com/phpBB/viewtopic.ph ... 29#1911629
GRIZZLY_NL
Registered User
Posts: 21
Joined: Sat Mar 29, 2003 10:01 pm

Re: Kill

Post by GRIZZLY_NL »

smurfix wrote: I don't know whether this has been mentioned yet, because I got lost scanning through 34 pagesof random hints ;-) but even if you don't heavily optimize searches there's something you can do about speeding them up.

Consider the code with $delete_search_ids in search.php. Examine what it does. Yes, it creates a DELETE ... NOT IN query. Except that the part after the NOT IN has every session ID in existence in it. That's a bit excessive -- 3000 sessions (not that much when people tend to stay logged in) equals one 110-KByte query.

Sorry, but that's stupid. Throw out this code now, and replace it with one table change

Code: Select all

 alter table SEARCH_TABLE add timestamp timestamp, add index ts(timestamp);
and one cronjob

Code: Select all

delete from SEARCH_TABLE where timestamp < date_sub(now(), interval 30 minute);
or whatever. Problem solved.

If you want to get even more fancy, you can use a multi-table statement:

Code: Select all

delete SEARCH_TABLE from SEARCH_TABLE left join SESSIONS_TABLE on SEARCH_TABLE.session_id = SESSIONS_TABLE.session_id where SESSIONS_TABLE.session_id is null;
You don't need the timestamp column with this solution, but this way will be slower.

If you want to get even more fancy, then forget about all of this, move your tables to InnoDB, and add a FOREIGN KEY between the two, with ON DELETE CASCADE.


Thanks, after upgrading to MySQL 5.0.16 I noticed that MySQL was locking up when ppl used the search funtion. I've already disabled wildcard search, but I noticed the search_results was causing troubles.
After replacing the "NOT IN" code with your left join it works great.
This could be related to MySQL5 though, I've never had this problem with version 4 and 4.1 but I don't want to downgrade. The rest is working superb though.

Another "NOT IN" query is also taking some seconds to run (this one is called upon user registration in /includes/usercp_register.php):

Code: Select all

$sql = 'DELETE FROM ' .  CONFIRM_TABLE . " 
				WHERE session_id NOT IN ($confirm_sql)";
I wonder if this one could be replaced by a left join as well?
Are left joins faster than NOT IN statements?
User avatar
EverettB
Registered User
Posts: 326
Joined: Fri Aug 01, 2003 7:11 pm
Location: North America

Post by EverettB »

On the subject of caching data, here is what I am currently using in various locations. Example of caching the category data in index.php:

Code: Select all

# 
#-----[ OPEN ]------------------------------------------ 
#
index.php

#
#-----[ FIND ]------------------------------------------
# 
$sql = "SELECT c.cat_id, c.cat_title, c.cat_order
	FROM " . CATEGORIES_TABLE . " c 
	ORDER BY c.cat_order";
if( !($result = $db->sql_query($sql)) )
{
	message_die(GENERAL_ERROR, 'Could not query categories list', '', __LINE__, __FILE__, $sql);
}

$category_rows = array();
while( $category_rows[] = $db->sql_fetchrow($result) );
$db->sql_freeresult($result);

#
#-----[ REPLACE WITH ]------------------------------------------
# 
/*
$sql = "SELECT c.cat_id, c.cat_title, c.cat_order
	FROM " . CATEGORIES_TABLE . " c 
	ORDER BY c.cat_order";
if( !($result = $db->sql_query($sql)) )
{
	message_die(GENERAL_ERROR, 'Could not query categories list', '', __LINE__, __FILE__, $sql);
}

$category_rows = array();
while( $category_rows[] = $db->sql_fetchrow($result) );
$db->sql_freeresult($result);
*/

// Start caching
$category_rows = array();
include($phpbb_root_path . 'cache/categories.'.$phpEx);
if (sizeof($category_rows) == 0)
{
	$sql = "SELECT SQL_CACHE c.cat_id, c.cat_title, c.cat_order
			FROM " . CATEGORIES_TABLE . " c
			ORDER BY c.cat_order";
    if( !$result = $db->sql_query($sql) )
    {
		message_die(GENERAL_ERROR, 'Could not query categories list', '', __LINE__, __FILE__, $sql);
    }
	$file_data = '<? $category_rows = ' . format_array($db->sql_fetchrowset($result)) . ' ?>';
	$db->sql_freeresult($result);
	$handle = fopen($phpbb_root_path . 'cache/categories.'.$phpEx, 'w');
	@flock($handle, LOCK_EX);
	fwrite($handle, $file_data);
	@flock($handle, LOCK_UN);
	fclose($handle);
	include($phpbb_root_path . 'cache/categories.'.$phpEx);
}
// End caching
This assumes you have the format_array function in phpBB3:

Code: Select all

# 
#-----[ OPEN ]------------------------------------------ 
#
includes/functions.php

#
#-----[ FIND ]------------------------------------------
# 
?>

# 
#-----[ BEFORE, ADD ]------------------------------------------ 
#
// Stolen from phpBB3
function format_array($array)
{
	$lines = array();
	foreach ($array as $k => $v)
	{
		if (is_array($v))
		{
			$lines[] = "'$k'=>" . format_array($v);
		}
		elseif (is_int($v))
		{
			$lines[] = "'$k'=>$v";
		}
		elseif (is_bool($v))
		{
			$lines[] = "'$k'=>" . (($v) ? 'TRUE' : 'FALSE');
		}
		else
		{
			$lines[] = "'$k'=>'" . str_replace("'", "\\'", str_replace('\\', '\\\\', $v)) . "'";
		}
	}
	return 'array(' . implode(',', $lines) . ')';
}
caspert_ghost
Registered User
Posts: 199
Joined: Thu Apr 21, 2005 1:58 pm
Location: China
Contact:

Post by caspert_ghost »

Hi Lanzer,
WOW I Am impressed. in such a short time you maged to get over 1b post and upwards of 100k reg. users. That is impressive in ANY book.
I assume the target to younger generations with the avatar mod is the number 1 attraction, along with the rpg theme. Very nice and well done.
I have but 3 questions for you...
1: you mentioned earlier you are NOT a php or sql expert, mind sharing your idea on best practices for learning, or is it "dive in and see if it works LOL?

2: I saw your enterview (both of them) and you mentioned you only have about 100 moderators, may I ask how many admins or is it just you. (do you run Jr admins as well?

3: What advice would you give to prepare someone if their board reaches the sky as yours has?

Thank you

**still staggering over your site numbers**
mgutt
Registered User
Posts: 346
Joined: Tue Sep 21, 2004 2:54 pm
Location: Germany, Sankt Augustin
Contact:

Post by mgutt »

in viewtopic.php are "u.user_session_time" & "u.user_allow_viewonline" not a part of the original mysql query.

i think i don't need them or?

As we are having only one big spam thread i've done it like that:

Code: Select all

//-- mod : spam topic ----------------------------------------------------------
//-- delete
/*
$sql = "SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_firstname, u.user_ebayname, u.user_car, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, p.*,  pt.post_text, pt.post_subject, pt.bbcode_uid
	FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . POSTS_TEXT_TABLE . " pt
	WHERE p.topic_id = $topic_id
		$limit_posts_time
		AND pt.post_id = p.post_id
		AND u.user_id = p.poster_id
	ORDER BY p.post_time $post_time_order
	LIMIT $start, ".$board_config['posts_per_page'];
*/
//-- add
if ( $topic_id != 6311 )
{
	$sql = "SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_firstname, u.user_ebayname, u.user_car, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, p.*,  pt.post_text, pt.post_subject, pt.bbcode_uid
		FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . POSTS_TEXT_TABLE . " pt
		WHERE p.topic_id = $topic_id
			$limit_posts_time
			AND pt.post_id = p.post_id
			AND u.user_id = p.poster_id
		ORDER BY p.post_time $post_time_order
		LIMIT $start, ".$board_config['posts_per_page'];
}
else
{
	$p_array = array(); 
	$sql = "SELECT p.post_id FROM " . POSTS_TABLE . " p WHERE p.topic_id = $topic_id $limit_posts_time LIMIT $start, " . $board_config['posts_per_page']; 
	if ( !($result = $db->sql_query($sql)) ) 
	{ 
	   message_die(GENERAL_ERROR, "Could not obtain post index information.", '', __LINE__, __FILE__, $sql); 
	} 
	while (list($p_id) = $db->sql_fetchrow($result)) { 
	    $p_array[] = $p_id; 
	} 
	$post_index = implode(",",$p_array);
	// Datenbankabfrage
	$sql = "SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_firstname, u.user_ebayname, u.user_car, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, p.*,  pt.post_text, pt.post_subject, pt.bbcode_uid 
   	FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . POSTS_TEXT_TABLE . " pt 
  	 WHERE p.post_id in ($post_index) 
  	    AND pt.post_id = p.post_id 
  	    AND u.user_id = p.poster_id 
 	  ORDER BY p.post_time $post_time_order";
}
//-- fin mod : spam topic ------------------------------------------------------

2.
jema wrote: I have tried this one on a number of forums, but then I found a forum where the last posts were appearing on the first page. Looking at the queries I think the first sql query needs "ORDER BY p.post_time $post_time_order" in order to return the correct ids. Has anyone else had this issue?


same problem for me.. but not the last one is the first.. they are mixup up and the queries ate my complete ram in 1 minute of using. so its at the moment slowier than the original one :?
Forums: Honda || phpBB Categories Hierarchy
Mods: Spamfilter against bot registrations || Seo Urls
NBishop
Registered User
Posts: 119
Joined: Sun Feb 13, 2005 9:11 am
Location: Johnstown, PA (USA)
Name: Nate
Contact:

Post by NBishop »

caspert_ghost wrote: 2: I saw your enterview (both of them) and you mentioned you only have about 100 moderators, may I ask how many admins or is it just you. (do you run Jr admins as well?


Offhand I can answer that question actually.

GAIA currently has really 6 Administrators along with I believe atleast 100-110 Moderators, can't say how many of them are actually active though *sweat*

mgutt: Er, where did you get that 'Spam Topic' MOD from, I checked back a few pages (granted i've not checked out this thread in a couple/few months unfortunatly), but I didn't see anything about that within this particular thread, I'm just sorta confused as to if you're trying to provide a tweak of some sort or ask a question for that code you have o_O;;

As for your comment about the quote from the first page, hrm... I wanna say that from what i've noticed, the original 'tweaks' on the front page don't seem to really do what they are stated to anymore, most likely due to the fact that when Lanzer had done those tweaks, the version of phpBB being used at the time was like 2.0.4-2.0.6, though I never started using phpBB until 2.0.10 I believe it was, I'd imagine there are many code changes which might make those original tweaks not very workable anymore >.<;
America's ArmysT Projects
- nate / synapt -
mgutt
Registered User
Posts: 346
Joined: Tue Sep 21, 2004 2:54 pm
Location: Germany, Sankt Augustin
Contact:

Post by mgutt »

the spam topic mod is mine. i reduces the "special sql query" to my spam topic with the specific id. it has now ca. 40.000 posts since the last 3 month. so it explodes in mysql queries.

ok, you mean i have to read the last 30 pages to get an update for the actual version of phpBB :cry:

maybe someone out there with a summary :D

but since 2.0.4 the query seems to be exactly the same :?

lanzer edit you post please Image

Image
Forums: Honda || phpBB Categories Hierarchy
Mods: Spamfilter against bot registrations || Seo Urls
Locked

Return to “2.0.x Discussion”