Question about optimizing PHP&MySQL

[Jei]

I wrote my own webboard like 4 years ago. It does work to some extent but I must say my code were complete mess.
http://www.all-final.com/forum/list.php?forumid=

For the most part, I only studied from 1 PHP4 book and made the whole thing with a lot of scripts ripped from phpBB (which I only understand about 10% of it's code >< )
I have a spring break coming up so I decided to start my whole thing from scratch.

The first thing I'm really curious is that, as I read, repeatedly openning small connections to MySQL many times is not a good way. Making 1 big connection for all the information is the way to go. Is this true?

If I am trying to generate a forum viewing page for example. Currently I have a table ThreadID. And a seperate table for reply.
I use for each reply loop to pull out each member's and reply's information. So if there's 40 replies in a page it basicly makes 41 connection. 1 for the threadID and 40 more for each replies >< . ya, so ugly

Now I'm wondering how to optimize this part? I can't think of a way to request information for the whole thead in 1 shot. Any suggestions?

[Balfree]

Re: Question about optimizing PHP&amp;MySQL

It is true because you should keep the strain to a minimum, thats it basically..

I'm not so sure how to optimize it, but i do know whbere you can get some help, ozzu.com, huge community, very helpful and nice people.

[Jei]

Re: Question about optimizing PHP&amp;MySQL

thanks for the link I'll check it out.

[neighbortaru]

Re: Question about optimizing PHP&amp;MySQL

yes 1 big query is almost always better than many small queries. it's not so much strain as it's the network traffic. DB's are optimized for queries and whatnot whereas the network will always be the network. Think of it like this query time (x) + network (n) versus 40x + 4n. Which is better?

have you looked into using JOIN to query across your two tables?

[Balfree]

Re: Question about optimizing PHP&amp;MySQL

yea queries, my host has like 10gb space and absolutely no bdwidht limits, but they do limit my queries per hour, a non-optimized script would pwn my account faster than you can say omg

[Medalink]

Re: Question about optimizing PHP&amp;MySQL

You might be looking for something like this

<?php

// I allways DEFINE tables so I can easily change them later. This will allways be included from a file named constants where I also define all my golbal info.

define("TABLE", "test");

// I allways place things in functions so its easy to call them.

function displayPosts() {

// Holds all my DB connection info and makes a connection to

global $database; MySQL.
$q = "SELECT * "
."FROM ".TABLE." ORDER BY ID DESC LIMIT 25;";
$result = $database->query($q);

// Just in case theres an error.

$num_rows = mysql_numrows($result);
if(!$result || ($num_rows < 0)){
echo "Error displaying info";
return;
}
if($num_rows == 0){

// Or if the DB empty.

echo "Database table empty";
return;
}

// Add your loop only to the results not the Connection.

for($i=0; $i<$num_rows; $i++){
$value1 = mysql_result($result,$i,"db_value1");
$value2 = mysql_result($result,$i,"db_value2");
$value3 = mysql_result($result,$i,"db_value3");
$value4 = mysql_result($result,$i,"db_value4");

echo("$value1 $value2 $value3 $value4 ");
}
}

// Now simply put displayPosts(); where you want the posts to output.
// Also note at line 6, where your query is, be sure to adjust the limit you want the db to output.
?>

Hope that helps, let me know if you have any questions, I am a 6 year PHP/MySQL programmer.

Medalink

[Jei]

Re: Question about optimizing PHP&amp;MySQL

No I'm not aware of join at all. Let me research about the command.
The only thing I know is to query multiple tables if they share some common value.
like, if I have ID value and both table A+b has ID field, I can query

SELECT FROM A, B WHERE A.ID = B.ID

or something like this.

Most often times there are couple tables I need to get value from previous one in order to point to the next. Is this the case where I can use join?
Like, use ID to query from table A to get value Date, use Date to query from table B to get names, use names to query from C to get next data etc?

[neighbortaru]

Re: Question about optimizing PHP&amp;MySQL

try this: http://www.w3schools.com/sql/sql_join.asp

[Jei]

Re: Question about optimizing PHP&amp;MySQL

awesome. thanks neighbor ^^