Hi,

I run a website which has a somewhat large forum (not compared to WHT ofc). I have several HUGE topics with over 20,000 replies to each (the biggest one is at 46,000+ replies).

The forum script is custom made (by myself) using php and mysql and is it designed to do as few updates as possible (it does NOT for example keep track of which user is online, or how many times a topic is shown). However it is beginning to slow down now.

Especially in LARGE topics, as mentioned, is where I have some trouble. They are implemented as multipage topics, where the page loading time is slower the higher number page you are on.

The query simply gets all message records in the topic, joined to the user table and displays it. The specific page is then retrieved using LIMIT x,40 (where x is 40*pagenum). So basically I think the problem is with LIMIT becomming slower the higher the x value is.

My question is, is there any, more efficient way, of retrieving multipage topics?

Is your indexes set right? When you select you do somethink like...

SELECT whatever FROM forum_messages fm WHERE fm.thread_id = ID ORDER BY fm.date_posted LIMIT PAGE_NUM*40,40

Or? Try to set an index on date_posted....

There is an index on date_posted (time in my db). It isnt really necesarry though because records are stored with oldest first in the db anyway (so it is already stored in the correct order). It is does not make any difference if I simply do not order the results: they come out in the right order still, but the query times are stil like this:

LIMIT 0,40: 0.0031 seconds
LIMIT 40000,40: 1-3 seconds

Hmmm,

I may have found the issue. When I just retrieve the messages without joining the table to the user table, it is very quick (around 0.02 seconds) even with LIMIT 40000,40. So I suppose the reason is that mysql joins the ENTIRE tables (which is very very bad considering the number of rows in the message table) together first and then searches out the particular messages.

please show the sql you use to select messages :)

Used to be:

SELECT message.id, body, time, message.user, message.email, message.ip,
message.userid, sigopt, editusr, edittime,
user.name, user.created, user.posts, user.signa, user.sigdef,
user.ava, user.avaext
FROM message,user WHERE tid = <topic id> AND visible = 1 AND user.id = message.userid ORDER BY time LIMIT 0,40

But now I just retrieve the messages, and then if it is a registered member that posted a given message, I query the database server and get his details (# of posts, join date etc.)

(hmm why am I retrieving the email and ip? I'm not using it :D )

you can try using the explain query feature in mysql.

A query for the task you explained should not really be using more than 40 joins.

message.userid should be an index. You said you're using an index only on time column.

There is some option of Explain Plan in Oracle
I am sure there must be alike in Mysql too
Using that you can know which part of the query is taking so much time!

The explain support that superprogram mentioned is in MySQL as well. Just prepend "EXPLAIN" to your SELECT query and see what MySQL tells you.

The results returned can be slightly confusing for complex queries, but it usually gives a good insight into what indexes MySQL is actually using, and exactly how much work it's having to do. You may be surprised at what optimizations MySQL is or isn't using.