Number of pages

[Migrated From Jforum.net]

How do you know how many pages of topics/posts you have? So far, the only way i know is to do a COUNT() of how many rows are in there, and then dividing that number by your topics/posts per page index.

But doing counts in every query limits performance.
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

I keep a field in jforum_forums and jforum_topics to know how many records there are.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

So you need to use a transaction for each query in order to retrieve the real current value?

I don't want to introduce transactions for every select, just inserts... So i will probably do just nextpage - previous page on mortiforo...

Damn, i need to get an account :P
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

I don't make any special query just to get the value. I have them in memory or they come when I fetch topics data.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

The problem is that my "go to last post" feature gets broken if i use pages, since my code can't know in which page the last post is.

I am stuck on this, since sqlmap can't know the number of pages i have, the only way for me is to just get all the rows in a table.

It isn't much really since i haven't seen topics bigger than 200 posts, so i can just retrieve the 200 posts and just show the first 15 or something. At least that way i can know how many pages there are and fix my problem.

My tutor told me it wouldn't be reliable to have an attribute counting the number of posts, etc (this is called desnormalizing) since row locking should be done to avoid retrieving possible wrong values.
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

I don't completely aggree about the "desnormalizing" point. See, if I didn't have a field to track the number of topics and messages, I would need to do a COUNT() *every* time, and that's waste of system resources.

Normalization is important, but if you get off the point, you will end with much worst performance than when having two or three fields "not normalized". In fact, normalization is not an easy task, and have direct impacts on how your application is designed, since it reflects your object model.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

I agree with Rafael, if the value is read many write few, use a field to store the current value if a good idea to save the computing resource.

[originally posted on jforum.net by andowson]

[Migrated From Jforum.net]

maybe i explained myself wrong, there are few cases when desnormalization is useful, and this is one of them.
But according to my tutor you need to do row locking in order for those values to be reliable when reading them. And maybe that would be counter productive depending on your load.
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Row locking? I think this is handled by DBMS automatically. Programmer no need to do it.
[originally posted on jforum.net by andowson]

[Migrated From Jforum.net]

I guess, instead you configure by hand, moderns database servers will not lock rows when reading. You can set special arguments to the sql statement when executing it, but the default, as far as I know, is that it will not have any lock for reads.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Exactly the reason why this may be needed when just viewing stuff, since it's a read only operation
[originally posted on jforum.net by Anonymous]