Post table ideas - Breaking it up.

[ekool]

The biggest performance bottleneck seems to be in the post table and the fact that it gets locked upon writes/searches, etc.

Why not break up the post table into 10 seperate post tables, such as post0, post1, post2, post3 all the way to post9.

The postid would determine which post table the post goes into.

Upon a search, east post table gets searched one at a time in a series, so that at any given time only one post table is locked at any given time.

[Zachery]

You'd have to search all of them if you wanted to search the entire forum which means you have to search all 10 tables instead of 1...

[ekool]

You'd have to search all of them if you wanted to search the entire forum which means you have to search all 10 tables instead of 1...



Upon a search, east post table gets searched one at a time in a series, so that at any given time only one post table is locked at any given time.

Yes, I noted that in my first post. See above.

[feldon23]

The post table gets locked when it's being searched?

[ekool]

The post table gets locked when it's being searched?
Yes.

[Paul M]

Yes, any table subject to a SELECT has a read lock on it, which prevents (queues) any write locks until the query has finished (other read locks are allowed, but, on most systems they will queue behind a previously waiting write lock).