[solved] Query lenght
Moderator: General Moderators
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
[solved] Query lenght
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?
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.
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
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 wrote:um... wouldnt a query such as (i dont remember the schema) select count(posts) where postread = 0
suffice?
-
nickvd
- DevNet Resident
- Posts: 1027
- Joined: Thu Mar 10, 2005 5:27 pm
- Location: Southern Ontario
- Contact:
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
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
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.
But initial question about the maximum lenght of a query still remains.
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
http://dev.mysql.com/doc/mysql/en/table-size.html
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
Thanks.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
Well thats plenty enough. Each read message/thread stands for about 30 characters in the query, so it's tens of thousands of those...
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
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.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.
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.
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am