Skip to main content ↑ ↓
Topic: Quick question regarding Recount Posts in ManageMaintenance (Read 7714 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Quick question regarding Recount Posts in ManageMaintenance

Can someone explain this last step in ManageMaintenance? It seems needlessly complicated, IMHO:

Code: (below this line) [Select]
// final steps ... made more difficult since we don't yet support sub-selects on joins
AFAICS the whole part is to reset all members with no post in elkarte_messages to 0. Wouldn't it make more sense to just reset all members by:
Code: [Select]
update elkarte_members SET posts = 0;
and then recount the post for members, This is already done in the code above the mentioned line.
Last Edit: April 07, 2013, 03:38:17 pm by TE
Thorsten "TE" Eurich
------------------------

Re: Quick question regarding Recount Posts in ManageMaintenance

Reply #1 –

I'm responsible for that needless complexity ... you need to look closer there is more of that  O:-)

Yes that section is used to find members who have a post count >0 that should not ... the ones the admin entered a value for, ones that had all their posts moved to a non-post count  board etc, etc  tends to be the one off's rather than the normal.

I believe what you are saying would work, the only "issue" may be after you zero out the entire table and things go wrong, its very wrong.  The current way does not zero the few one off's until its completed counting/updating .. but then the query is quite a bit more complicated, (and more fun).

Re: Quick question regarding Recount Posts in ManageMaintenance

Reply #2 –

Quote from: Spuds – I'm responsible for that needless complexity ... you need to look closer there is more of that  O:-)

Yes that section is used to find members who have a post count >0 that should not ... the ones the admin entered a value for, ones that had all their posts moved to a non-post count  board etc, etc  tends to be the one off's rather than the normal.

I believe what you are saying would work, the only "issue" may be after you zero out the entire table and things go wrong, its very wrong.  The current way does not zero the few one off's until its completed counting/updating .. but then the query is quite a bit more complicated, (and more fun).
oh, thanks for explanation. Would you be fine with my solution?

The current version has probably other issues:
- The MySQL user needs the CREATE TEMPORARY TABLE permission, which isn't always given
- Methinks creating a temp table and using an OUTER JOIN is extremly slow compared to my solution , but that's just a guess.. I would need to validate it via EXPLAIN with populated large members & messages tables.
- On big boards that queries would probably fail (again, just a guess) because there is no "overload" protection implemented. We'd need  a pause($_REQUEST['start'], $increment) function, similiar to other areas where overload can be an issue.
Thorsten "TE" Eurich
------------------------

Re: Quick question regarding Recount Posts in ManageMaintenance

Reply #3 –

The only real downside to what you suggested  is that after you zero all the members, any post count based permissions and group icons, etc will be wrong until the maintenance run is complete and fixed only as it progresses, so a failure or timeout is a real issue.  There could be other temporary side effects but I'm not sure.

I'm certainly fine with your approach as it will be more robust, certainly less likely to fail for the edge cases that the other query was trying to catch.  My thought was it was a nice to have knowing it was most likely was going to return no members (which is why I did not put in a pause loop for that section) ... my assumption about the result set size and frequency of occurrence could be all wrong as well.

I'm pretty sure we use CREATE TEMPORARY TABLE in several other places as well so that problem will remain in those areas but that's a separate issue.

All that said, If no one has any OMG concerns, I'd say go for it  :)

Re: Quick question regarding Recount Posts in ManageMaintenance

Reply #4 –

What about creating a real (temporary) table and process some entries at a time?
And when finished delete everything?
Bugs creator.
Features destroyer.
Template killer.

Re: Quick question regarding Recount Posts in ManageMaintenance

Reply #5 –

What ya doing ... testing to see if split / merge works :P

ETA ... Ema was to fast for me ! ... I should have done a QFT

Re: Quick question regarding Recount Posts in ManageMaintenance

Reply #6 –

O:-)
Bugs creator.
Features destroyer.
Template killer.

Re: Quick question regarding Recount Posts in ManageMaintenance

Reply #7 –

LOL, surprisingly the OUTER JOIN is faster  ;D
OUTER JOIN: 0.3549 sec
UPDATE TABLE: 0.9491 sec

Tested on my Asus EeePC (DualCore Atom, 2 GB Ram, SATA HDD)
Forum:
~270.000 messages,
~13.000 topics
~5000 members
- 110 boards

I leave it as it is.
Thorsten "TE" Eurich
------------------------