Optimize this Code!

New Member Joined: Jan 03, 2010 Posts: 16

Hello all,
Could someone look at this code and tell me how it can be improved

Code:

##############################################################


## MOD Title: Thanks mod and PhiliWeb Hide BBCode


## MOD Author: Kinfule < kinfule@lycos.es > (Javier B) http://kinfule.tk


## MOD Description: This mod will change the way the hide BBcode works.


##                           To see the hidden message the user will have to thank the topic


##                           instead of posting on it.


##


## MOD Version: 1.0.1


##


## Installation Level: Easy


## Installation Time: 5 Minutes


## Files To Edit: 3


## Included Files: n/a


## License: http://opensource.org/licenses/gpl-license.php GNU General Public License v2


##############################################################


## For security purposes, please check: http://www.phpbb.com/mods/


## for the latest version of this MOD. Although MODs are checked


## before being allowed in the MODs Database there is no guarantee


## that there are no security problems within the MOD. No support


## will be given for MODs not found within the MODs Database which


## can be found at http://www.phpbb.com/mods/


##############################################################


## Author Notes:


##


##############################################################


## MOD History:


##


##   2005-03-12 - Version 1.0.0


##      - Release, My users are more important.


##   2005-03-12 - Version 1.0.1


##      - Fixed Bug wich hided mesagge from topic poster.


##


##############################################################


## Before Adding This MOD To Your Forum, You Should Back Up All Files Related To This MOD


##############################################################


#


#-----[ OPEN ]------------------------------------------


#


posting.php





#


#-----[ FIND ]------------------------------------------


#


$sql = "SELECT p.poster_id, p.topic_id


FROM " . POSTS_TABLE . " p


WHERE p.topic_id = $topic_id


AND p.poster_id = " . $userdata['user_id'];





#


#-----[ REPLACE WITH ]------------------------------------------


#


$sql = "SELECT p.topic_id, t.topic_id


FROM " . THANKS_TABLE . " p, ". TOPICS_TABLE ." t


WHERE p.topic_id = $topic_id


AND p.user_id = " . $userdata['user_id']. "


OR t.topic_id = $topic_id


AND t.topic_poster = " . $userdata['user_id'];





#


#-----[ FIND ]------------------------------------------


#


else { $sql = "SELECT p.poster_id, p.topic_id


FROM " . POSTS_TABLE . " p


WHERE p.topic_id = $topic_id


AND p.poster_id = " . $userdata['user_id'];








#


#-----[ REPLACE WITH ]------------------------------------------


#


else { $sql = "SELECT p.topic_id, t.topic_id


FROM " . THANKS_TABLE . " p, ". TOPICS_TABLE ." t


WHERE p.topic_id = $topic_id


AND p.user_id = " . $userdata['user_id']. "


OR t.topic_id = $topic_id


AND t.topic_poster = " . $userdata['user_id'];








#


#-----[ OPEN ]------------------------------------------


#


viewtopic.php





#


#-----[ FIND ]------------------------------------------


#


$sql = "SELECT p.poster_id, p.topic_id


FROM " . POSTS_TABLE . " p


WHERE p.topic_id = $topic_id


AND p.poster_id = " . $userdata['user_id'];





#


#-----[ REPLACE WITH ]------------------------------------------


#


$sql = "SELECT p.topic_id, t.topic_id


FROM " . THANKS_TABLE . " p, ". TOPICS_TABLE ." t


WHERE p.topic_id = $topic_id


AND p.user_id = " . $userdata['user_id']. "


OR t.topic_id = $topic_id


AND t.topic_poster = " . $userdata['user_id'];








#


#-----[ OPEN ]------------------------------------------


#


includes/topic_review.php





#


#-----[ FIND ]------------------------------------------


#


$sql = "SELECT p.poster_id, p.topic_id


FROM " . POSTS_TABLE . " p


WHERE p.topic_id = $topic_id


AND p.poster_id = " . $userdata['user_id'];





#


#-----[ REPLACE WITH ]------------------------------------------


#


$sql = "SELECT p.topic_id, t.topic_id


FROM " . THANKS_TABLE . " p, ". TOPICS_TABLE ." t


WHERE p.topic_id = $topic_id


AND p.user_id = " . $userdata['user_id']. "


OR t.topic_id = $topic_id


AND t.topic_poster = " . $userdata['user_id'];





#


#-----[ SAVE/CLOSE ALL FILES ]------------------------------------------


#


# EoM

I would like to use this MOD but once i edit all the files my forum loads super sloooooooooow , if i revert back to original code the forum loads normal
Any help will greatly be appreciated

Posted: Sat Jun 12, 2010 3:46 pm

Hi,

Really, this should not be causing your forums to load slowly. I could imagine if the mysql server was slow, this would increase the time to run these extra queries, but really, all you are doing is changing them.

You could, in theory, optimize the code with joins and such, but it's difference would be minimal, as in you should not see a difference.

maybe a .1 second increase, not something you'd truly notice.

Is your mysql host on the same server?

Posted: Sat Jun 12, 2010 5:37 pm

gregexp wrote:

Hi,

Really, this should not be causing your forums to load slowly. I could imagine if the mysql server was slow, this would increase the time to run these extra queries, but really, all you are doing is changing them.

You could, in theory, optimize the code with joins and such, but it's difference would be minimal, as in you should not see a difference.

maybe a .1 second increase, not something you'd truly notice.

Is your mysql host on the same server?

thank you for your reply
these are my load times without this mod
forum index 0.47
viewforum 0.54
viewtopic 0.53

and these are with this mod
forum index 0.49
viewforum 0.55
viewtopic 39.5

i triple checked the edits
forum and mySql are on the same server
thanks for the help

Worker Joined: Aug 26, 2007 Posts: 236

If you don't get a suggestion here you can try this forum for phpbb2:
http://phpbb2refugees.com/

Posted: Sun Jun 13, 2010 7:25 am

I'd open up PHYmyadmin and copy the queries there (especially the ones associated with viewtopic.php). You'd need to modify the queries a bit to get them to run in PHPmyadmin since you wouldn't have variables such as $topic_id or constants such as THANKS_TABLE. I'd see how big the result set is for both the original query and the one you've replaced it with. I suspect your "or" condition may be resulting in some humongo result sets but that's just a guess.

Posted: Sun Jun 13, 2010 12:45 pm

fkelly wrote:

I'd open up PHYmyadmin and copy the queries there (especially the ones associated with viewtopic.php). You'd need to modify the queries a bit to get them to run in PHPmyadmin since you wouldn't have variables such as $topic_id or constants such as THANKS_TABLE. I'd see how big the result set is for both the original query and the one you've replaced it with. I suspect your "or" condition may be resulting in some humongo result sets but that's just a guess.

could you please give me a step by step on how i can do this in PhpMyAdmin?
thanks for all the help

Posted: Sun Jun 13, 2010 2:07 pm

I can't "teach" PHPmyadmin here. Basically, if you open it up and open up a SQL window (it's on one of the tabs within) you could paste the query from the code you posted. Then substitute for the variables and constants. So,

Code:

$sql = "SELECT p.topic_id, t.topic_id


FROM " . THANKS_TABLE . " p, ". TOPICS_TABLE ." t


WHERE p.topic_id = $topic_id


AND p.user_id = " . $userdata['user_id']. "


OR t.topic_id = $topic_id


AND t.topic_poster = " . $userdata['user_id'];

would become something like:

Code:

SELECT p.topic_id, t.topic_id


FROM nuke_bbthanks p,  nuke_bbtopics t


WHERE p.topic_id = some topic id you know


AND p.user_id = some userid you want to test with"


OR t.topic_id = some topic id you know


AND t.topic_poster = the same userid

I can't test this cause I don't even have a Thanks table.

Run the query, fix any syntax errors and see what kind of results you get. Then do the same for the other form of the query (the one before you changed it for the thanks table).

Posted: Sun Jun 13, 2010 2:22 pm

nuke_q7,

I was going to suggest changing TOPIC_TABLE to POST_TABLE, but in reality, I don't see that making much of an improvement.

Is your thanks table large?

if so, we might be able to change the code up a bit.

this can't be the entire mod, so if you could link us to the entire mod, we may be able to help further by optimizing the entire code.

Posted: Sun Jun 13, 2010 3:32 pm

fkelly wrote:

I can't "teach" PHPmyadmin here. Basically, if you open it up and open up a SQL window (it's on one of the tabs within) you could paste the query from the code you posted. Then substitute for the variables and constants. So,

Code:

$sql = "SELECT p.topic_id, t.topic_id


FROM " . THANKS_TABLE . " p, ". TOPICS_TABLE ." t


WHERE p.topic_id = $topic_id


AND p.user_id = " . $userdata['user_id']. "


OR t.topic_id = $topic_id


AND t.topic_poster = " . $userdata['user_id'];

would become something like:

Code:

SELECT p.topic_id, t.topic_id


FROM nuke_bbthanks p,  nuke_bbtopics t


WHERE p.topic_id = some topic id you know


AND p.user_id = some userid you want to test with"


OR t.topic_id = some topic id you know


AND t.topic_poster = the same userid

I can't test this cause I don't even have a Thanks table.

Run the query, fix any syntax errors and see what kind of results you get. Then do the same for the other form of the query (the one before you changed it for the thanks table).

fkelly,
thank you for your help, i understand that you can't teach phpMyAdmin here but i am not a professional coder thats why i asked for step by step help, i tried to run a query but was getting errors, maybe your advise would work but i am not sure how to execute your directions but thank you for the try

gregexp wrote:

nuke_q7,

I was going to suggest changing TOPIC_TABLE to POST_TABLE, but in reality, I don't see that making much of an improvement.

Is your thanks table large?

if so, we might be able to change the code up a bit.

this can't be the entire mod, so if you could link us to the entire mod, we may be able to help further by optimizing the entire code.

its this mod or a nuke version of it
http://www.phpbb.com/community/viewtopic.php?t=373805
but its been many years since i have installed it and am not 100% positive
the mod works fine but i just wanted to add the above hack to the mod
i really appreciate everyones help

Posted: Sun Jun 13, 2010 3:40 pm

Showing rows 0 - 29 (28,257 total, Query took 0.0005 sec)

thats the size of nuke_bbthanks

Posted: Sun Jun 13, 2010 4:01 pm

Nuke_q7 ... I can see one syntax mistake in the code I quoted you to try: correcting that and putting in some real values you could try something like this:

Code:

SELECT p.topic_id, t.topic_id


FROM nuke_bbthanks p,  nuke_bbtopics t


WHERE p.topic_id = 3


AND p.user_id = 2


OR t.topic_id =3


AND t.topic_poster = 2

You can browse (using PHPmyadmin) the nuke_bbtopics table to make sure you have a topic id which has a value of 3. Also the nuke_users table to make sure you have a user_id of 2. That's what I somewhat arbitrarily substituted in while taking out a superfluous quote I had left in the code.

If you want to pursue this but get errors you could let us know what they are. PHPmyadmin is really an essential tool to get to know if you want to do anything more than basic administration of a Nuke site. And I'd say that implementing mods is something more than basic administration.

As I stated earlier, I'm thinking that maybe you are getting a very large result set. You might need parentheses to pair the and and or conditions, something in the nature of:

Code:

SELECT p.topic_id, t.topic_id


FROM nuke_bbthanks p,  nuke_bbtopics t


WHERE (p.topic_id = 3


AND p.user_id = 2)


OR (t.topic_id =3


AND t.topic_poster = 2)

Not knowing how bbthanks is supposed to work it's hard for me to say.

Posted: Sun Jun 13, 2010 4:12 pm

once again thank you for your reply
i did this

Code:

SELECT p.topic_id, t.topic_id


FROM nuke_bbthanks p, nuke_bbtopics t


WHERE p.topic_id = 28935


AND p.user_id = 2


OR t.topic_id =28935


AND t.topic_poster = 2

and these are the results
MySQL returned an empty result set (i.e. zero rows). ( Query took 90.5589 sec )
i will now try the second one

Posted: Sun Jun 13, 2010 4:19 pm

Code:

SELECT p.topic_id, t.topic_id


FROM nuke_bbthanks p, nuke_bbtopics t


WHERE (p.topic_id = 28935


AND p.user_id = 2)


OR (t.topic_id =28935


AND t.topic_poster = 2)

MySQL returned an empty result set (i.e. zero rows). ( Query took 89.4609 sec )

Posted: Sun Jun 13, 2010 6:05 pm

You may need to "browse" your tables and find a topic_id number that really exists. Then a user_id that really exists. Then combine them to move on. Or try the two sets of conditions that are separated by parentheses individually and see what they result in. You can use PHPmyadmins search feature to look for topic_id's = x in one table (the bbtopics one. Look at the SQL that generates then combine it with the SQL that's generated by looking for userid=x.

Posted: Sun Jun 13, 2010 10:03 pm

fkelly wrote:

You may need to "browse" your tables and find a topic_id number that really exists. Then a user_id that really exists. Then combine them to move on. Or try the two sets of conditions that are separated by parentheses individually and see what they result in. You can use PHPmyadmins search feature to look for topic_id's = x in one table (the bbtopics one. Look at the SQL that generates then combine it with the SQL that's generated by looking for userid=x.

the topic_id exists and user_id exists, its just taking a super long for it to tell me that i haven't thanked that topic
i started to eliminate some code from the MOD and i figured out that everything loads fine (fast) if i remove this code from the entire MOD

Code:

OR t.topic_id = $topic_id

as far as i can tell the MOD works fine without that line of code.
the only problem is that the hidden content is now hidden to the topic starter, where with the above line of code the hidden content is always visible to the topic starter. Maybe someone can tell me what else this code does?
is there a way to rewrite this above piece of code?
thanks for all the help

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Make sure that the t.topic_id field is indexed. Without it, the OR has to do a full table scan.

Posted: Mon Jun 14, 2010 8:15 am

Raven wrote:

Make sure that the t.topic_id field is indexed. Without it, the OR has to do a full table scan.

thank you for your reply Raven,
Sorry for such a dumb question but how do I check if t.topic_ID is indexed and if its not how would I index it?

Posted: Mon Jun 14, 2010 8:35 am

Here you find a thread with a similar discussion which might help. The picture in the bottom shows the index of a table in the database:
http://phpbb2refugees.com/viewtopic.php?t=615

Posted: Sat Jun 19, 2010 6:18 pm

Hello all,
Sorry for the delayed reply
As far as indexing is this what you are referring to??

I have created this index but i am still having the same issue.
If this is not correct could you please tell me what should be indexed or if anyone has any other ideas.
Thanks to everyone for all the help

Posted: Sat Jun 19, 2010 6:47 pm

I'm not 100% positive, but you may want to index both topic_id and user_id.

This should help significantly.

Posted: Sat Jun 19, 2010 7:36 pm

gregexp wrote:

I'm not 100% positive, but you may want to index both topic_id and user_id.

This should help significantly.

i have indexed topic_id and user_id and still same problem Sad

Posted: Sun Jun 20, 2010 8:39 am

At this point, we need to get the output of the stuff.

Find:

Code:




$sql = "SELECT p.topic_id, t.topic_id


FROM " . THANKS_TABLE . " p, ". TOPICS_TABLE ." t


WHERE p.topic_id = $topic_id


AND p.user_id = " . $userdata['user_id']. "


OR t.topic_id = $topic_id


AND t.topic_poster = " . $userdata['user_id'];

below that, find the line that should read:

Code:




$result=$db->sql_query($sql);

add the following right below that:

Code:




$mcontent=$db->sql_fetchrow($result)


mail('YOUR_EMAIL','content from SQL query',$sql."\n\n".print_r($mcontent,true));

(replace YOUR_EMAIL with your e-mail address)
After that, I would like to see what it's doing with this code.

Yes, there may be a way of optimizing it, this will give you an example of a query that is being run, and you can use that in phpmyadmin to test the speed of the query, and find ways to improve it.

Right after you make these edits, load the viewtopic.php page, then immediately remove the code that was added(2 lines).

Posted: Sun Jun 20, 2010 8:57 am

thanks for your help.
i have added the above lines only in viewtopic.php and get this error

Code:

Parse error: syntax error, unexpected T_STRING in /home/******/public_html/*********/modules/Forums/viewtopic.php on line 370

Posted: Tue Jun 22, 2010 7:09 am

hi, sorry about that

this line:

$mcontent=$db->sql_fetchrow($result)

Should be:
$mcontent=$db->sql_fetchrow($result);

Just a ; at the end.

Sorry about that.

Posted: Sat Jun 26, 2010 8:16 am

gregexp wrote:

hi, sorry about that

this line:

$mcontent=$db->sql_fetchrow($result)

Should be:
$mcontent=$db->sql_fetchrow($result);

Just a ; at the end.

Sorry about that.

i have received this in my email

Code:




SELECT p.topic_id, t.topic_id


FROM nuke_bbthanks p, nuke_bbtopics t


WHERE p.topic_id = 28945


AND p.user_id = 15


OR t.topic_id = 28945


AND t.topic_poster = 15

then i ran that in phpmyadmin and got this result

Code:

Showing rows 0 - 29 (16,859 total, Query took 58.7279 sec)

and here's a screen shot

thanks for your help