By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,939 Members | 1,638 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,939 IT Pros & Developers. It's quick & easy.

Forum efficiency

P: n/a
Hello all,

I have attempted to write my own forum using PHP and MySQL. It works,
but the efficiency is far from ideal. It takes a long time to load e.g.
the list with threads in a certain forum.

I think the problem has to do with the fact that I have several SQL
calls in nested loops, so if a large thread list has to be loaded, there
are a lot of SQL queries to be executed. I have posted the code that
initialises the forum below, so you can see what I mean.

My question: is there a more efficient way to initialise my forum or is
it better to begin from scratch?

Here's the code:

function Forum($id) {
// initialize the threads-attribute
$this->threads = array();

// get the information for this forum from the database
$con = dbConnect();
if ($con) {
$sql = "SELECT * FROM forums WHERE forum_id = '$id'";
$query = mysql_query($sql);
$res = mysql_fetch_object($query);
$this->forum_id = $res->forum_id;
$this->name = $res->name;
$this->desc = $res->description;

// get the contents of the threads-attribute from the database
$sql = "SELECT * FROM threads WHERE forum_id = '$id'";
$query = mysql_query($sql);
while ($res = mysql_fetch_object($query)) {
$thread_id = $res->thread_id;
$subject = $res->subject;
// get the messages for this thread
$messages = array();
$sql2 = "SELECT * FROM messages WHERE thread_id =
'$res->thread_id'";
$query2 = mysql_query($sql2);
while ($res2 = mysql_fetch_object($query2)) {
$msg_id = $res2->msg_id;
$author_id = $res2->author_id;
$thread_id = $res2->thread_id;
$post_date = $res2->post_date;
$contents = $res2->contents;
$msg = new Message($msg_id, $author_id, $thread_id, $post_date,
$contents);
$messages[] = $msg;
}
$thread = new Thread($thread_id, $subject, $messages);
$this->threads[] = $thread;
}
}
}

--
Daan Stolp
Jul 17 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Daan wrote:
Hello all,

I have attempted to write my own forum using PHP and MySQL. It works,
but the efficiency is far from ideal. It takes a long time to load e.g.
the list with threads in a certain forum.


I haven't really looked at your code, but this is what I would, and
already do, use:

Each topic has a header record:
id - int
subject - varchar(x)
...

Each message has a record:
topicid - int |
id - int | unique(topicid + id)
parent - int - relates to message.id or null/0 for first post
ts - timestamp
body - text
...
...

Then your display query is:
select * from messages
where topicid=$topicid
order by parent,ts

But you will have to load all the records and output them recursively to
get the thread sorted properly.
Jul 17 '05 #2

P: n/a
On Tue, 17 Feb 2004 13:28:27 +0100, "Daan"
<da*******@NOSPAM.newmail.net> wrote:
Hello all,

I have attempted to write my own forum using PHP and MySQL. It works,
but the efficiency is far from ideal. It takes a long time to load e.g.
the list with threads in a certain forum.

I think the problem has to do with the fact that I have several SQL
calls in nested loops, so if a large thread list has to be loaded, there
are a lot of SQL queries to be executed. I have posted the code that
initialises the forum below, so you can see what I mean.

My question: is there a more efficient way to initialise my forum or is
it better to begin from scratch?


If you look at what most other Forum scripts do, they have two tables
- one for topics, and one for posts.

It has a number of advantages - like staging your statistics (views
etc) in the topics table rather than working them out.

Welcome to anti-textbook coding - writing fast code generally means
finding any trick you can regardless of it's difficult to then read
the code...
Jon Beckett
Jul 17 '05 #3

P: n/a
Daan:
Hello all,

I have attempted to write my own forum using PHP and MySQL. It works,
but the efficiency is far from ideal. It takes a long time to load e.g.
the list with threads in a certain forum.

I think the problem has to do with the fact that I have several SQL
calls in nested loops, so if a large thread list has to be loaded, there
are a lot of SQL queries to be executed. I have posted the code that
initialises the forum below, so you can see what I mean.


I'd recommend to use the MySQL explain command to see if any of your queries
are performing less than optimal: http://www.mysql.com/doc/en/EXPLAIN.html

If your queries are performing at their max, it's time to start looking at
smarter ways to store and retrieve the data

André Nęss
Jul 17 '05 #4

P: n/a
Hello,

On 02/17/2004 09:28 AM, Daan wrote:
I have attempted to write my own forum using PHP and MySQL. It works,
but the efficiency is far from ideal. It takes a long time to load e.g.
the list with threads in a certain forum.

I think the problem has to do with the fact that I have several SQL
calls in nested loops, so if a large thread list has to be loaded, there
are a lot of SQL queries to be executed. I have posted the code that
initialises the forum below, so you can see what I mean.

My question: is there a more efficient way to initialise my forum or is
it better to begin from scratch?


Sure. Note down this thought:

- The way to write fast database applications is to avoid accessing
database as much as possible.

What I mean is that usually database accesses are the slowest factor in
database driven sites. So, you need to only access databases for the
essential.

In most content sites, including forums, you have much more database
accesses for retrieving data than to change that data.

So, what you can do to minimize the database reading accesses is to
cache the pages that you generate from reading the database data in
local disk files.

Then, whenever the data is changed, just removed the cached page files
to force that it is regenerated next time it is needed for display.

In this case, you may want to try this class that is meant to cache
generic data. You can use it to cache whole pages or just excerpts of
each page.

In your case, whenever somebody accesses a page that display a forum
thread, just use the cache class to verify whether the cache class
exists and is still valid.

If it does not exist, just regenerate it and pass its HTML (or just the
forum thread listing excerpt) to the class so it can store it safely
even when you have a busy site with many simultaneous accesses.

If the cache file exists and is upto date, just ask the class to
retrieve the HTML data from the cache files and display it.

Whenever a user updates the thread, just call the function of the class
that voids the cache data file, to force the thread listing page caches
to be regenerated from database next time these pages are accessed.

The speedup can be huge. So you have an idea, this technique is so
efficient that the site where this class is hosted is now holding more
than 6000 cached excerpts of pages.

http://www.phpclasses.org/filecache

--

Regards,
Manuel Lemos

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/

PHP Reviews - Reviews of PHP books and other products
http://www.phpclasses.org/reviews/

Metastorage - Data object relational mapping layer generator
http://www.meta-language.net/metastorage.html
Jul 17 '05 #5

P: n/a
On Tue, 17 Feb 2004, Daan wrote:
I think the problem has to do with the fact that I have several SQL
calls in nested loops, so if a large thread list has to be loaded, there
are a lot of SQL queries to be executed. I have posted the code that
initialises the forum below, so you can see what I mean.


You can get away without the nested DB queries, if you are prepared to cap
the maximum number of branches and total depth of the tree for a thread.
Have an indexed varchar column (sort_key, say) that's pretty wide, and glue
pieces together so that it sorts properly. Basically you take the parent's
sort_key and tack on, say, 'aaa' for the first child, 'aab' for the second
and so forth. For a varchar(900) with each child consuming 3 characters you
could get 300 deep and 62^3 (for mixed-case alphanumeric) wide at each
level. You'll want to twiddle those numbers to fit your real situation.

Then you just ask have one query to get all the postings back in the right
order:

SELECT things FROM postings WHERE ... ORDER BY sort_key;

This was a nice hack in the ArsDigita Community System back in the day.
You'll turn some stuff up if you search the Web for 'sort_key bboard' with
sql, data-model, design and such like.

--
ash
a-k
.... "Hey, imp!" shouted Vlad, the imp hailer.

Jul 17 '05 #6

P: n/a
Hi Daan!

On Tue, 17 Feb 2004 13:28:27 +0100, "Daan"
<da*******@NOSPAM.newmail.net> wrote:
Hello all,

I have attempted to write my own forum using PHP and MySQL. It works,
but the efficiency is far from ideal. It takes a long time to load e.g.
the list with threads in a certain forum.

I think the problem has to do with the fact that I have several SQL
calls in nested loops, so if a large thread list has to be loaded, there
are a lot of SQL queries to be executed. I have posted the code that
initialises the forum below, so you can see what I mean.

My question: is there a more efficient way to initialise my forum or is
it better to begin from scratch?

Maybe have a look at nested sets.

HTH, Jochen

Jul 17 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.