keep unread flags

All new MODs released in our MOD Database will be announced in here. All support for released MODs needs to take place in here. No new MODs will be accepted into the MOD Database for phpBB2
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

On February 1, 2009 this forum will be set to read only as part of retiring of phpBB2.

Rating:

Excellent!
72
75%
Very Good
16
17%
Good
4
4%
Fair
0
No votes
Poor
4
4%
 
Total votes: 96

Merlin Sythove
Registered User
Posts: 2339
Joined: Tue Mar 16, 2004 7:42 am

Post by Merlin Sythove »

My complete Index page takes less than 0.02 seconds. And this query is part of the index page, obviously. Can you give some statistics of your board, like how many posts are on it, how many users on line usually?

My board has 150.000 posts, usually 5-10 users online at the same time (5 minute interval)

I find the solution interesting, I don't know enough of the internals of the MySQL engine to see WHY this would be so much faster. In my case it wouldn't make much difference but I will look into it and recommend it if further confirmation of its improvements is forthcoming.

The only optimisation that I can see offhand, is, assuming that evaluating AND statements is aborted after the first false, is to put the simplest and most likely to fail statements first, so the second query would become

Code: Select all

        AND (p.post_time <= " . $tracking_time . ")
        $sql_unreads
        AND t.topic_moved_id = 0"
Furthermore, the code itself can be rewritten because the logic of making $sql_unreads separately, is no longer there. These are minor things though.

Finally, in the SECOND loop, you don't need to check if the topic really is unread, you have already specified that via the query (I think) and a further test is not needed, simplifying the second loop.
Need custom work done? Pimp My Forum!
Merlin Sythove
Registered User
Posts: 2339
Joined: Tue Mar 16, 2004 7:42 am

Post by Merlin Sythove »

OK, I have it installed and working, I'll test it for a while, so far I cannot see any improvement in time. You may want to list your version of MySQL too (are you using MySQL?) I hope others will test it and give some timings!
(Please note that the birthday mod is notorious for slowing down your index page so be careful what you time!!)
Need custom work done? Pimp My Forum!
User avatar
Bigwebmaster
Registered User
Posts: 56
Joined: Sun Dec 22, 2002 1:48 am
Location: Seattle, WA
Contact:

Post by Bigwebmaster »

Our board has about 310,000 posts with just about 20,000 members. Within a 5 minute interval there are usually 150 to 200 members online at a time. This is not counting all of the search engine bots constantly pounding the server as well, which does put additional load.

Yeah there are obviously things that can be changed with the code I wrote. It was just a quick fix to see if I could improve performance and for our board it does improve performance a big deal.

I will take your suggestions to further optimize the query (and code) when I get some more time. Thanks for checking it.
User avatar
Bigwebmaster
Registered User
Posts: 56
Joined: Sun Dec 22, 2002 1:48 am
Location: Seattle, WA
Contact:

Post by Bigwebmaster »

Merlin Sythove wrote: OK, I have it installed and working, I'll test it for a while, so far I cannot see any improvement in time. You may want to list your version of MySQL too (are you using MySQL?) I hope others will test it and give some timings!
(Please note that the birthday mod is notorious for slowing down your index page so be careful what you time!!)


We were using MySQL 4, but actually just upgraded to MySQL 5 today. Performance for us is about the same with both versions.

We do not have the birthday mod, and when I was testing the speed I was simply testing one query versus the other in a separate PHP file that I made solely for the test.
Merlin Sythove
Registered User
Posts: 2339
Joined: Tue Mar 16, 2004 7:42 am

Post by Merlin Sythove »

Bigwebmaster wrote: Our board has about 310,000 posts with just about 20,000 members. Within a 5 minute interval there are usually 150 to 200 members online at a time. This is not counting all of the search engine bots constantly pounding the server as well, which does put additional load.


Right, that would put up the server load 10-20 times compared to my board :D

Is there a way to find out if doing 2 small queries instead of 1 large one, is not really a time problem but a queueing problem? Like giving the impression of multitasking? If there are 5 users in front of you waiting for their first query to process, but those queries are only a very fast half query, you are served faster. Whilst you process your results (your loop to fill the array, otherwise a similar thing is done by the SQL engine) someone else has their 2nd query processed, and when you are ready for your 2nd half, there is a time slot since other people got into other time slots. What I'm trying to say is that I'm interested (if possible) to know if the speed improvement in your case is due to the fact that the query is split into two halves, or whether the OR statement really is inefficient.

Keep us posted!
Need custom work done? Pimp My Forum!
User avatar
Bigwebmaster
Registered User
Posts: 56
Joined: Sun Dec 22, 2002 1:48 am
Location: Seattle, WA
Contact:

Post by Bigwebmaster »

That is an interesting thought. I mean what you are saying does make sense because what would frequently happen with the board is that one query would lock the tables just long enough so that the next query had to wait, then that query once it finally started running would lock the tables for a few seconds before the next could start. If you get enough simultaneously queries all hitting at once this could get really backed up quickly. So its important that queries get executed fast and do not lock tables. Usually if a query creates a tmp table that will also cause the tables to lock, so preventing that from happening is always good.

This query in question could possibly be faster for small boards, but when you get larger it becomes more efficient breaking it into two SQL queries. At least from tests I have done it shows that. If you goto this thread:

http://www.phpbb.com/phpBB/viewtopic.php?t=135383

You will see they do that all the time there. Break large inefficient queries into smaller efficient queries so that less stress is being put on the MySQL server. It does put a little load back on the PHP side since PHP is having to execute more code now, but the overall performance is still improved.
asinshesq
Registered User
Posts: 6266
Joined: Sun Feb 22, 2004 9:34 pm
Location: NYC
Name: Alan

Post by asinshesq »

None of this would ever have occurred to me, and I have to say I find it fascinating. Thanks for bringing this up. By the way, have you done most of the fixes described in the optimaztion for big boards topic you cited?
Merlin Sythove
Registered User
Posts: 2339
Joined: Tue Mar 16, 2004 7:42 am

Post by Merlin Sythove »

Thanks for the input, I'll evaluate it further. You really DO need to know if, and where, MySQL is inefficient, in order to find these tweaks. I used to be able to do it with assembler and Visual Basic, knowing how the internals worked, and write fast and small code even when the code you type, may sometimes be bigger. Anyone have a link to a performance page for MySQL?
Need custom work done? Pimp My Forum!
blackrat
Registered User
Posts: 63
Joined: Thu Mar 23, 2006 10:07 pm
Contact:

compatibolity wit Topic Calendar?

Post by blackrat »

If you are running the split topic type mod, the topic calendar mod, the profile control panel or any other mod that installs a file called d 'functions_topic_list.php' in your includes/ directory, you need to install the mod_for_functions_topics_list after you have already installed this mod (that mod has not yet been written and may never be)


I didn't anderstand this. Is it now compatible or can be made compatilble with Topic Calendar (Sort Topics, Topic Icon), I also wnat to install?

You should add compatibility infos on the first page of this thread, so it's easy to find.
Last edited by blackrat on Fri May 19, 2006 11:02 am, edited 1 time in total.
blackrat

My board:
www.game-multimedia.com
net83it
Registered User
Posts: 500
Joined: Sun Jan 15, 2006 2:42 pm
Location: Sicily
Contact:

Re: compatibolity wit Topic Calendar?

Post by net83it »

blackrat wrote:
If you are running the split topic type mod, the topic calendar mod, the profile control panel or any other mod that installs a file called d 'functions_topic_list.php' in your includes/ directory, you need to install the mod_for_functions_topics_list after you have already installed this mod (that mod has not yet been written and may never be)


what? i have a 'functions_topic_list.php' file in my includes/ directory. what i have to do ?
daisy84
Registered User
Posts: 129
Joined: Thu Sep 29, 2005 6:03 am

Post by daisy84 »

I'm sorry I just have a quick question.
I upgrade the forum from 2.019 to 2.20
and on the Keep Read Unflaged I saw that part of the code wasn't intact in search.php

Code: Select all

# 
#-----[ FIND ]--------------------------------------------- 
# Line 214 
            if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author)))) 
            { 
               $search_author = ''; 
            } 

            $search_author = str_replace('*', '%', trim($search_author)); 
             

# 
#-----[ REPLACE WITH ]--------------------------------------------- 
# 
            $search_author = str_replace('*', '%', trim($search_author)); 

            if( ( strpos($search_author, '%') !== false ) && ( strlen(str_replace('%', '', $search_author)) < 3 ) ) 
            { 
               $search_author = ''; 
            } 


# 
#-----[ FIND ]--------------------------------------------- 
# Line 437 
         if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author)))) 
         { 
            $search_author = ''; 
         } 

         $search_author = str_replace('*', '%', trim(str_replace("\'", "''", $search_author))); 

# 
#-----[ REPLACE WITH ]--------------------------------------------- 
# 
         $search_author = str_replace('*', '%', trim($search_author)); 

         if( ( strpos($search_author, '%') !== false ) && ( strlen(str_replace('%', '', $search_author)) < 3 ) ) 
         { 
            $search_author = ''; 
         }


So I came searching and found this

Code: Select all

Replace with the following: 

# 
#-----[ FIND ]--------------------------------------------- 
# Line 437 
         $search_author = str_replace('*', '%', trim(str_replace("\'", "''", $search_author))); 

# 
#-----[ REPLACE WITH ]--------------------------------------------- 
# 
         $search_author = str_replace('*', '%', trim($search_author)); 
Is this the only changes that have to be done to an upgrade from 2.0.19 to 2.0.20?

Because the code changes in the file phpbb 2.0.19 to 2.0.20 text files say this

Code: Select all

# 
#-----[ FIND ]--------------------------------------------- 
# Line 214 
            if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author)))) 
            { 
               $search_author = ''; 
            } 

            $search_author = str_replace('*', '%', trim($search_author)); 
             

# 
#-----[ REPLACE WITH ]--------------------------------------------- 
# 
            $search_author = str_replace('*', '%', trim($search_author)); 

            if( ( strpos($search_author, '%') !== false ) && ( strlen(str_replace('%', '', $search_author)) < 3 ) ) 
            { 
               $search_author = ''; 
            } 


# 
#-----[ FIND ]--------------------------------------------- 
# Line 437 
         if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author)))) 
         { 
            $search_author = ''; 
         } 

         $search_author = str_replace('*', '%', trim(str_replace("\'", "''", $search_author))); 

# 
#-----[ REPLACE WITH ]--------------------------------------------- 
# 
         $search_author = str_replace('*', '%', trim($search_author)); 

         if( ( strpos($search_author, '%') !== false ) && ( strlen(str_replace('%', '', $search_author)) < 3 ) ) 
         { 
            $search_author = ''; 
         }
That is 2 edits.

But here you mention only 1 edit

Code: Select all

#-----[ FIND ]--------------------------------------------- 
# Line 437 
         $search_author = str_replace('*', '%', trim(str_replace("\'", "''", $search_author))); 

# 
#-----[ REPLACE WITH ]--------------------------------------------- 
# 
         $search_author = str_replace('*', '%', trim($search_author));


I hope I made sense. :oops:
asinshesq
Registered User
Posts: 6266
Joined: Sun Feb 22, 2004 9:34 pm
Location: NYC
Name: Alan

Post by asinshesq »

The post you refer to distinguishes between someone who already is at 2.0.20 and is not installing keep unread for the first time versus someone who already has keep unread installed on 2.0.19 and now wants to upgrade to 2.0.20. The code I posted works so long as you use the right one for your situation.

Here it is again for others who don't want to search for it:

1. If you are trying to upgrade from 2.0.19 to 2.0.20 on a board that already has the keep unread mod installed, replace the part of the upgrade from 2.0.19 to 2.0.20 mod that tells you to do this:

Code: Select all

#
#-----[ FIND ]---------------------------------------------
# Line 214
				if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author))))
				{
					$search_author = '';
				}

				$search_author = str_replace('*', '%', trim($search_author));
				

#
#-----[ REPLACE WITH ]---------------------------------------------
#
				$search_author = str_replace('*', '%', trim($search_author));

				if( ( strpos($search_author, '%') !== false ) && ( strlen(str_replace('%', '', $search_author)) < 3 ) )
				{
					$search_author = '';
				}


#
#-----[ FIND ]---------------------------------------------
# Line 437
			if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author))))
			{
				$search_author = '';
			}

			$search_author = str_replace('*', '%', trim(str_replace("\'", "''", $search_author)));

#
#-----[ REPLACE WITH ]---------------------------------------------
#
			$search_author = str_replace('*', '%', trim($search_author));

			if( ( strpos($search_author, '%') !== false ) && ( strlen(str_replace('%', '', $search_author)) < 3 ) )
			{
				$search_author = '';
			}
with the following:

Code: Select all

#
#-----[ FIND ]---------------------------------------------
# Line 437
			$search_author = str_replace('*', '%', trim(str_replace("\'", "''", $search_author)));

#
#-----[ REPLACE WITH ]---------------------------------------------
#
			$search_author = str_replace('*', '%', trim($search_author));

2. If you are trying to install keep unread on a board that has already been upgraded to 2.0.20, then:

(a) change the part of keep unread that says this:

Code: Select all

#
#-----[ FIND ]------------------------------------------------
#
				if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author))))
				{
					$search_author = '';
				}

				$search_author = str_replace('*', '%', trim($search_author));

#
#-----[ REPLACE WITH ]------------------------------------------------
#
				$search_author = str_replace('*', '%', trim($search_author));
so that it instead says this:

Code: Select all

#
#-----[ FIND ]------------------------------------------------
#
				$search_author = str_replace('*', '%', trim($search_author));

				if( ( strpos($search_author, '%') !== false ) && ( strlen(str_replace('%', '', $search_author)) < 3 ) )
				{
					$search_author = '';
				}

#
#-----[ REPLACE WITH ]------------------------------------------------
#
				$search_author = str_replace('*', '%', trim($search_author));
and (b) change the part of keep unread that says this:

Code: Select all

#
#-----[ FIND ]------------------------------------------------
#
			if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author))))
			{
				$search_author = '';
			}

			$search_author = str_replace('*', '%', trim(str_replace("\'", "''", $search_author)));

#
#-----[ REPLACE WITH ]------------------------------------------------
#
			$search_author = str_replace('*', '%', trim(str_replace("\'", "''", $search_author)));
so that it instead says this:

Code: Select all

#
#-----[ FIND ]------------------------------------------------
#
			$search_author = str_replace('*', '%', trim($search_author));

			if( ( strpos($search_author, '%') !== false ) && ( strlen(str_replace('%', '', $search_author)) < 3 ) )
			{
				$search_author = '';
			}

#
#-----[ REPLACE WITH ]------------------------------------------------
#
			$search_author = str_replace('*', '%', trim($search_author));
Samsa
Registered User
Posts: 3
Joined: Fri May 19, 2006 5:20 pm

Post by Samsa »

Hello folks,

This mod strangely doesn't work on my forum and I don't understand why. I have no error message of any kind, the features of the mod appear normally on the board but I can't post anymore and when I logout, I can't login anymore: in both cases the posting page and the index page show a blank screen.

Precision: in the database, the field "user_unread_topics" remains NULL.

Thanks for your help.
asinshesq
Registered User
Posts: 6266
Joined: Sun Feb 22, 2004 9:34 pm
Location: NYC
Name: Alan

Post by asinshesq »

Samsa wrote: Hello folks,

This mod strangely doesn't work on my forum and I don't understand why. I have no error message of any kind, the features of the mod appear normally on the board but I can't post anymore and when I logout, I can't login anymore: in both cases the posting page and the index page show a blank screen.

Precision: in the database, the field "user_unread_topics" remains NULL.

Thanks for your help.


My standard advice if you didn't use easymod: go back to your backup files and then install this with easymod.
Samsa
Registered User
Posts: 3
Joined: Fri May 19, 2006 5:20 pm

Post by Samsa »

asinshesq wrote:
Samsa wrote:Hello folks,

This mod strangely doesn't work on my forum and I don't understand why. I have no error message of any kind, the features of the mod appear normally on the board but I can't post anymore and when I logout, I can't login anymore: in both cases the posting page and the index page show a blank screen.

Precision: in the database, the field "user_unread_topics" remains NULL.

Thanks for your help.


My standard advice if you didn't use easymod: go back to your backup files and then install this with easymod.

I didn't use easymod indeed but I have a clean board and so I simply copied the changed files that are supplied for this mod. I just modified the search.php following your instructions.
Post Reply

Return to “[2.0.x] MOD Database Releases”