Find one millionth post

[Square Wheels]

My tiny little site will reach one million posts soon.

Two things.

The front page stats claim I am at 999,008 under Forums Statistics.  The database table forums_posts says I am at ~891,007.  Which is correct?  That's a huge discrepancy.

Also, any tips on a query to find the millionth post?

Thanks

[Nathan Explosion]

3 minutes ago, Square Wheels said:

The database table forums_posts says I am at ~891,007

That is the number of rows, which would account for deletions.

Look for the one with the ID of 1000000

[Square Wheels]

1 minute ago, Nathan Explosion said:

That is the number of rows, which would account for deletions.

Thanks, doesn't the Forum Statistics block read the row count from the database?  There is more than a 100k difference.  I rarely delete threads, no way I've deleted 100k posts.

[CoffeeCake]

14 minutes ago, Square Wheels said:

The front page stats claim I am at 999,008 under Forums Statistics.  The database table forums_posts says I am at ~891,007.  Which is correct?  That's a huge discrepancy.

InnoDB uses an estimation to display number of rows for things like SHOW TABLE STATUS. This is why you're likely seeing ~XXXXXX where ever you are seeing it. It's an approximation.

To get the actual number of rows in a particular table at a moment in time (forums_posts, for example), use: SELECT COUNT(1) FROM forum_posts

For a bit more of an in-depth here, check out https://dba.stackexchange.com/questions/17926/why-doesnt-innodb-store-the-row-count

If you were wanting to celebrate the 1,000,000th post, I'd go with @Nathan Explosion's suggestion and use the post ID of 1000000. This makes things super easy. The post ID increments with every write to the forums_posts table, so it would include deleted items in the count (and if you did anything like truncate the table when you were first setting things up--unlikely, but... including here as a caveat). Otherwise, if you want to exclude posts made in forums that aren't incrementing toward post counts or any administrative/staff forums, you could calculate the 1,000,000th post via a SQL query that excludes posts in those other forums, or those that have been hidden, etc.

 

[Square Wheels]

Thanks both,

It looks like I've deleted many posts - over a thousand.  I had a thread about women in bikinis, it had hundreds of posts and was not appropriate.  I deleted it.  Again, I never delete anything, but that thread had no redeeming value.  If people get mad and say, I demand you delete that thread, I just move it to a hidden section of the site.  I've been sued once and was required to turn over my database, I'm sure it can happen again.

My current count is 1001805.  I'll look for a query that will find the millionth record in the database.

[Square Wheels]

Hmm, I just ran this, and it found a record.  From the Forum Statistics block, I should not be there yet.

SELECT
    *
FROM
    forums_posts
ORDER BY
    pid
LIMIT 1 OFFSET 999999;

Just to be sure, I put in an obscene number (a billion) and it came back "MySQL returned an empty result set (i.e. zero rows). "

Did I already hit a million posts and the Forums Statistics is way off?

[Nathan Explosion]

Here's a theory, based on what I know about my own site

It's been around a long time, gone through a number of major IPB/IPS releasess.
forums_posts has 3,328,619 rows
The latest ID is 4,531,883
My forums statistics says 3,328,131

Which one is correctly stating how many posts have been made? I can 100% state that the latest ID is correct - that is exactly how many posts have been created in that table since the inception of the site.

Why the different numbers?

1) Pre-4.x, my total posts displayed in the equivalent widget on IPS 3.4.x was somewhere in the region of ~3,500,000
2) Over the years, some other admins were really into the whole pruning functionality to "keep the database optimised"
3) Back in 2006, database was corrupted and we lost a stack of posts and topics - I recovered enough of them to continue but lost a good chunk.
4) IPB pre-4.x had a "Recount and rebuild" functionality which allowed the number of posts on the forum to be recounted to what is what at that time, as opposed to a count of what has been done over time. It wasn't the total number of posts at the time, unless you forced it to be recounted...which I'd done a few times.
5) IPS 4.x now has the 'Forums Statistics' widget, and it counts the number as:
 - the number of records in forums_posts that are not queued plus
 - any posts that have been archived, if archiving is enabled (see 'forums_archive_posts') and are also not queued.

For me, the real "what is the 1,000,000th post?" is answered by:

select * from forums_posts where pid = 1000000 (Well, it would be if it hadn't been deleted/lost over the years)

[CoffeeCake]

1 hour ago, Square Wheels said:

SELECT
    *
FROM
    forums_posts
ORDER BY
    pid
LIMIT 1 OFFSET 999999;

What happens if you add WHERE queued = 0 to remove hidden posts? The forum statistics block will only count unhidden posts, plus any archived unhidden posts you have (if you have archiving turned on).

To replicate what the block does, use:

SELECT (SELECT COUNT(1) FROM `forums_posts` WHERE `queued` = 0) + (SELECT COUNT(1) FROM	`forums_archive_posts` WHERE archive_queued = 0) AS `PostCount`

 

For code reference, look at applications/forums/widgets/forumStatistics.php, in the render() function at line 46.

[CoffeeCake]

Thank you for this question. That statistics block takes an absurd amount of time to run, and having not looked in depth at it before, I've just sped up my forum homepage by a very significant amount by removing it.

[Square Wheels]

1 hour ago, Nathan Explosion said:

Here's a theory, based on what I know about my own site

It's been around a long time, gone through a number of major IPB/IPS releasess.
forums_posts has 3,328,619 rows
The latest ID is 4,531,883
My forums statistics says 3,328,131

Which one is correctly stating how many posts have been made? I can 100% state that the latest ID is correct - that is exactly how many posts have been created in that table since the inception of the site.

Why the different numbers?

1) Pre-4.x, my total posts displayed in the equivalent widget on IPS 3.4.x was somewhere in the region of ~3,500,000
2) Over the years, some other admins were really into the whole pruning functionality to "keep the database optimised"
3) Back in 2006, database was corrupted and we lost a stack of posts and topics - I recovered enough of them to continue but lost a good chunk.
4) IPB pre-4.x had a "Recount and rebuild" functionality which allowed the number of posts on the forum to be recounted to what is what at that time, as opposed to a count of what has been done over time. It wasn't the total number of posts at the time, unless you forced it to be recounted...which I'd done a few times.
5) IPS 4.x now has the 'Forums Statistics' widget, and it counts the number as:
 - the number of records in forums_posts that are not queued plus
 - any posts that have been archived, if archiving is enabled (see 'forums_archive_posts') and are also not queued.

For me, the real "what is the 1,000,000th post?" is answered by:

select * from forums_posts where pid = 1000000 (Well, it would be if it hadn't been deleted/lost over the years)

Sorry about the corruption and loss of data.  That's disappointing.  Sounds like you still have a successful site, at least based on post count.

6 minutes ago, Paul E. said:

What happens if you add WHERE queued = 0 to remove hidden posts? The forum statistics block will only count unhidden posts, plus any archived unhidden posts you have (if you have archiving turned on).

To replicate what the block does, use:

SELECT (SELECT COUNT(1) FROM `forums_posts` WHERE `queued` = 0) + (SELECT COUNT(1) FROM	`forums_archive_posts` WHERE archive_queued = 0) AS `PostCount`

 

For code reference, look at applications/forums/widgets/forumStatistics.php, in the render() function at line 46.

 

 

 

 

 

 

 

 

 

 

Matched exactly!

[Square Wheels]

On 10/12/2020 at 7:17 PM, Paul E. said:

What happens if you add WHERE queued = 0 to remove hidden posts? The forum statistics block will only count unhidden posts, plus any archived unhidden posts you have (if you have archiving turned on).

To replicate what the block does, use:

SELECT (SELECT COUNT(1) FROM `forums_posts` WHERE `queued` = 0) + (SELECT COUNT(1) FROM	`forums_archive_posts` WHERE archive_queued = 0) AS `PostCount`

 

For code reference, look at applications/forums/widgets/forumStatistics.php, in the render() function at line 46.

Ugh, @Paul E., any chance I could bug you to help my find the millionth post with your query?

Thanks

[Square Wheels]

19 minutes ago, Square Wheels said:

Ugh, @Paul E., any chance I could bug you to help my find the millionth post with your query?

Thanks

Maybe not as elegant as it could be, but since I have no archived posts, no join needed, search just the one table.

SELECT
    *
FROM
    forums_posts
WHERE `queued` = 0
ORDER BY
    pid
LIMIT 1 OFFSET 999999;

[Runar]

On 10/12/2020 at 11:02 PM, Square Wheels said:

I've been sued once and was required to turn over my database, I'm sure it can happen again.

I'm interested in hearing more about this. Is this something you can and want to talk about?

[Square Wheels]

25 minutes ago, Runar said:

I'm interested in hearing more about this. Is this something you can and want to talk about?

One of my sites is about clinical pathology.  One vendor was being sued by another vendor over price fixing.  They felt my site may have discussed these two vendors and their price fixing schemes (we hadn't) and the offended vendor subpoenaed my database.  Not a big deal, I just sent them a copy of the database.

[Runar]

27 minutes ago, Square Wheels said:

One of my sites is about clinical pathology.  One vendor was being sued by another vendor over price fixing.  They felt my site may have discussed these two vendors and their price fixing schemes (we hadn't) and the offended vendor subpoenaed my database.  Not a big deal, I just sent them a copy of the database.

Thank you for the explanation! I assume you're not in the EU, as sending a copy of the database to a third-party would be a rather big deal for me. With that being said, I understand your decision.

[Square Wheels]

2 hours ago, Runar said:

Thank you for the explanation! I assume you're not in the EU, as sending a copy of the database to a third-party would be a rather big deal for me. With that being said, I understand your decision.

It wasn't much of a decision, I was forced to do it.  I am in the States.