[solved] Query lenght

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

[solved] Query lenght

Post 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?
Last edited by Shendemiar on Wed Mar 23, 2005 10:13 am, edited 1 time in total.
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

um... wouldnt a query such as (i dont remember the schema) select count(posts) where postread = 0

suffice?
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post 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.
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.. :?
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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 .. :?
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post 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...
Post Reply