Optimize this Code!

nuke_q7 · 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 <

Only registered users can see links on this board!
Get registered or login to the forums!

> (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

gregexp · 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?

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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

slackervaara · Worker Joined: Aug 26, 2007 Posts: 234

If you don't get a suggestion here you can try this forum for phpbb2:

Only registered users can see links on this board!
Get registered or login to the forums!

fkelly · 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.

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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

fkelly · 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).

gregexp · 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.

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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

Only registered users can see links on this board!
Get registered or login to the forums!

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

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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

thats the size of nuke_bbthanks

fkelly · 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.

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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 )

fkelly · 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.

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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

Raven · Posted: Sun Jun 13, 2010 11:44 pm

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

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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?

slackervaara · Worker Joined: Aug 26, 2007 Posts: 234

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:

Only registered users can see links on this board!
Get registered or login to the forums!

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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

gregexp · 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.

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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

gregexp · 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).

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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

gregexp · 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.

nuke_q7 · New Member Joined: Jan 03, 2010 Posts: 16

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