Page 1 of 2

[solved] Query lenght

Posted: Sun Mar 20, 2005 12:05 pm
by Shendemiar
I made a script that determines the actual amount of unread POSTS in phpbb messageboard. Due to the boards build, it's not an simple task, and therefore the query has several clauses...

IT works fine, but on a hypotethical situation where user keps browser open for weeks and marks more and more threads and forums read, the query would get extremely large.

What's the default maximum lenght of a query in mysql, and what aspects affect it?

Posted: Sun Mar 20, 2005 12:27 pm
by nickvd
um... wouldnt a query such as (i dont remember the schema) select count(posts) where postread = 0

suffice?

Posted: Sun Mar 20, 2005 1:08 pm
by Shendemiar
nickvd wrote:um... wouldnt a query such as (i dont remember the schema) select count(posts) where postread = 0

suffice?
No, it's a mixture of message post_times in database, threads and forums marked as read at certain time stored in a cookie in an unexcluding way. Marking read status for all post for all members would take huge space in the database.

Posted: Sun Mar 20, 2005 2:25 pm
by nickvd
oh, i understand... i was under the impression that you wanted to list all the topics that haven't been read by anyone. (similar to this forums read all un answered questions...) I"d have to take a look at the schema again (i think i still have a leftover copy of it at home before i migrated to vbb... if you could explain in a little more detail exactly what you're aiming for, I may be able to help

Posted: Sun Mar 20, 2005 4:57 pm
by feyd
TOPICS_TABLE.topic_views holds the views made to a topic. I don't see any way of tracking reads on individual posts unless you added one in the POSTS_TABLE structure.. :?

Posted: Mon Mar 21, 2005 1:05 am
by Shendemiar
My script returns 1) the amount of Forums that have unreaded messages, 2) The amount of Threads that have unreaded messages, and 3) The amount of unread messages. It can also return a link to any unread message if i want to, but i only need the number of each at this point.

But initial question about the maximum lenght of a query still remains.

Posted: Mon Mar 21, 2005 7:32 am
by feyd
maximum records it can return? As far as I know, that's a function of your RAM mixed with how many other query results are still in memory when such a request is made.

Posted: Mon Mar 21, 2005 8:31 am
by timvw
according the comments in the mysql documentation a query can be as large as 1mb

http://dev.mysql.com/doc/mysql/en/table-size.html

Posted: Mon Mar 21, 2005 8:58 am
by Shendemiar
timvw wrote:according the comments in the mysql documentation a query can be as large as 1mb

http://dev.mysql.com/doc/mysql/en/table-size.html
Thanks.

Well thats plenty enough. Each read message/thread stands for about 30 characters in the query, so it's tens of thousands of those...

Posted: Mon Mar 21, 2005 9:10 am
by feyd
why not make it easier on yourself and the database server by adding a read count field to the POSTS_TABLE ?

All you'd need to do as far as code is concerned then is when the topic_views is updated, update all posts in the thread with a +1 count.. or just use the field as a flag .. :?

Posted: Mon Mar 21, 2005 11:15 am
by Shendemiar
feyd wrote:why not make it easier on yourself and the database server by adding a read count field to the POSTS_TABLE ?
Because the read/unread status must be individual for every user.

Posted: Mon Mar 21, 2005 11:19 am
by feyd
okay... so what about using the system they use for Olympus, a read table. Records thread/post id, time, and user id. When their session data is deleted, so is the read data.

Posted: Mon Mar 21, 2005 12:22 pm
by Shendemiar
feyd wrote:okay... so what about using the system they use for Olympus, a read table. Records thread/post id, time, and user id. When their session data is deleted, so is the read data.
I don't know about olympus, but phpbb only stores these to the database: "post time" for each message, "the last time the entire forum was visited" for each use.

All "marked as read" data goes into cookie. Each time a thread or forum is marked as read, an entry plus current time is added to the cookie, while the old stuff still remains also. Needless to say, with lots of these markings, the query can get long with many OR and AND clauses... But i did it... My script can tell you something like this:

You have 5 unread posts in 3 threads in 2 categories.

Posted: Mon Mar 21, 2005 1:21 pm
by feyd
I know how phpbb works.. Olympus is phpbb 3. In it, they have moved the read marking to a table to make it easier for all things to work well.

Posted: Mon Mar 21, 2005 3:10 pm
by Shendemiar
feyd wrote:I know how phpbb works.. Olympus is phpbb 3. In it, they have moved the read marking to a table to make it easier for all things to work well.
Oh, in what phase of development it is?

Good thing with moving the read status, not very handy at the moment...