Very slow page load after apgrade to drupal6 and User Stats 6.x-1.0-rc1

This is a difficult one. But I am very willing to optimise User Stats for larger sites. Here are some questions and things to consider:

1. What is the speed like when you load the thread a second time? After User Stats determines the number of post counts for a user, the value is stored. When the user creates a new node/comment we increment the stored number, instead of running the expensive SELECT COUNT(*) ... again. Basically, when the post counts for every user are stored, you shouldn't see this problem again.
2. User Stats will also pre-generate post counts on a cron run -- you may set how many records it updates per cron run in Administer -> Site configuration -> User stats settings, the default is 25 -- so it is possible to point your browser at http://example.com/cron.php and set the page to refresh every second; or just set cron to run every minute, until all post counts are stored.
3. I am interested in making the update query faster, but don't have many ideas, if you do then let me know! Things we could do:
   • provide a batch update facility in the admin screen;
   • use SELECT COUNT(cid) ... instead of SELECT COUNT(*) ..., this might be faster, in many cases, since the database could use the key cache already held in memory, instead of doing a table scan, it might be conceptually incorrect though, since we're counting the number of rows, not values of cid (see: http://www.mysqlperformanceblog.com/2008/09/20/a-common-problem-when-opt... and http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/);
   • seven seconds per-query is a long time, we might get better performance using a temporary table and many small steps in lieu of a JOIN, this would be particularly effective when storing the post counts of multiple users, during a cron run, or batch update for example;
   • check the query is using indexes and whether it could be sped up with any simple optimisations;
4. You wouldn't have seen this issue in Drupal 5 as the post counts would have been stored for all your users. We moved to a different database schema for the Drupal 6 version, meaning post counts need to be regenerated.

This is enough information for me to investigate, thanks.

I've done some testing and the query is about as fast as it can be, although it might be sped up by an extra index on nid, uid, status on the {comment} table. The problem is that messing around with core tables is a no-no (as some other module may come along and try to make similar changes, leading to incompatibilities), so I'm going to have to think of another way around the problem.

I dont understand what changed in module from 5.x that lead this slow effect.

Literally nothing has changed in the way posts are counted, check the queries, they are exactly the same. Not saying you're wrong, just that I don't know either.

2. I set cron run every minute.. Where I can see how much users left to be counted?

Currently there's nothing in the admin interface which shows how many users there are to be counted. I probably won't add such a feature for some time (unless someone pays me to do so).

Here's a query that will tell you roughly how many people are left to count:
select (select count(*) from users)-(select count(distinct uid) from user_stats_values);
(Note: you'll need to add any table prefixes, and this doesn't take into account people who've been banned and whatnot)

3. How about User titles (http://drupal.org/project/user_titles)?

I will check User Titles, thanks. :)

Am not holding out much hope though, I expect User Titles counts posts during cron, or when a new post is inserted. The key problem here is User Stats needs to show post counts in forum threads, on-demand, User Titles can do everything behind the scenes. :)

Now the topic is loading less than for a second with the switched off module and more than 20 seconds with the included.

I'm not sure I understand this. Are forum topics loading slowly, even after the post count has been stored? This is quite important, as I may need to improve the performance of other parts of User Stats!

I think I have the same problem. I crash the site every time I try to load IP or other userstat information.
Not sure what to do about it, but will ask a more technical friend, to read up on this issue - I'm sure he'll understand better than me.

We have our own dedicated server, and everything else usually runs smoothly, but this module seems to have some loading issues.
Using same version as described here.