(2023-08-16, 04:03 PM)lost puppy Wrote: the last time I did a 'Recount & Rebuild' on the Thank You Like it took over 23 hours (actually I stopped it at near 24 hours because it was getting slower and slower)!

It began by sending between 300 to 400 queries per second and I stopped it when it was taking near 30 seconds per 300 - 400 .... at this point I think that it still had approx 1 million to do!

(2023-08-16, 04:03 PM)lost puppy Wrote: How can I make this 'Recount' vastly quicker?

(2023-08-16, 04:03 PM)lost puppy Wrote: Else I do not think that I can do the upgrade as I may need to do a 'Recount'!

(2023-08-19, 11:40 AM)Laird Wrote:

(2023-08-16, 04:03 PM)lost puppy Wrote: the last time I did a 'Recount & Rebuild' on the Thank You Like it took over 23 hours (actually I stopped it at near 24 hours because it was getting slower and slower)!

It began by sending between 300 to 400 queries per second and I stopped it when it was taking near 30 seconds per 300 - 400 .... at this point I think that it still had approx 1 million to do!

Mmm, yeah, looking at the code, it's terribly inefficient in terms of database queries.

(2023-08-16, 04:03 PM)lost puppy Wrote: How can I make this 'Recount' vastly quicker?

It could be done in a few manual queries, each of which might take a little while to run. If necessary, I can come up with those queries for you, however...

(2023-08-16, 04:03 PM)lost puppy Wrote: Else I do not think that I can do the upgrade as I may need to do a 'Recount'!

...there's nothing about an upgrade that would entail the need for a recount if one wasn't already required.

(2023-08-20, 02:18 PM)lost puppy Wrote: It would be brilliant to have the manual queries to run?

(2023-08-21, 08:04 AM)Laird Wrote:

(2023-08-20, 02:18 PM)lost puppy Wrote: It would be brilliant to have the manual queries to run?

Attached is a script that hopefully will work for you. The instructions are in a comment block up the top. Please let me know how you go with it, and especially the time it takes the SQL to run on a board with the millions of thanks/likes that yours has. If it's successful, I'll probably add it to the plugin itself, in the plugin's scripts directory.

UPDATE REDACTED_threads t
SET    tyl_tnumtyls = IFNULL(
                             (
                              SELECT SUM(tyl_pnumtyls)
                              FROM   REDACTED_posts p
                              WHERE  p.tid = t.tid
                             ),
                             0
                            );
MySQL said: Documentation

#1054 - Unknown column 'tyl_tnumtyls' in 'field list'

(2023-08-21, 04:43 PM)lost puppy Wrote: There are 6 queries (I think lol) and the first 4 .... each took seconds (I mean the db states 0.0004 for each one)

(2023-08-21, 04:43 PM)lost puppy Wrote: Only problem was on the 5th query

(2023-08-21, 04:43 PM)lost puppy Wrote: But it would definitely seem that it worked anyway 

Taking 2 members:

On the real site forum Member 1 has 29671 'likes' >> but now on the 'test' site the member has 33690
Member 2 on the real forum has 381953 'likes' >> on 'test' site they now have 424704

Speed wise .... well what can I say ... last time it took near 24 hours and didn't complete (I had to stop it) 

This took seconds!!

Stunning ... thank you so very much ... you are a super star 

(2023-08-22, 01:32 AM)Laird Wrote:

(2023-08-21, 04:43 PM)lost puppy Wrote: There are 6 queries (I think lol) and the first 4 .... each took seconds (I mean the db states 0.0004 for each one)

Excellent - I was hoping it would be fast even on a site with millions of thanks/likes.

(2023-08-21, 04:43 PM)lost puppy Wrote: Only problem was on the 5th query

Oh, I see - that's because the tyl_tnumtyls column was mistakenly removed (by me) from the core threads table in version 3.1.0 (August, 2018) of the plugin, and only reinstated in the latest version 3.4.5 (August, 2023), to which you have not yet upgraded.

I've updated the script (as attached in my previous post) to handle scenarios like yours. Please test the new version of the script (1.1.0) and let me know whether the error is avoided this time.

(2023-08-21, 04:43 PM)lost puppy Wrote: But it would definitely seem that it worked anyway 

Taking 2 members:

On the real site forum Member 1 has 29671 'likes' >> but now on the 'test' site the member has 33690
Member 2 on the real forum has 381953 'likes' >> on 'test' site they now have 424704

Speed wise .... well what can I say ... last time it took near 24 hours and didn't complete (I had to stop it) 

This took seconds!!

Stunning ... thank you so very much ... you are a super star 

That's great news. Cheers for the positive feedback.

Error
SQL query: Copy


PREPARE stmt FROM @query;
MySQL said: Documentation

#1044 - Access denied for user 'REDACTED'@'%' to database 'information_schema'

(2023-08-22, 08:21 AM)lost puppy Wrote: I tested it and all was well until the 5th query (creation of tyl_tnumtyls column) and the error given was:

Error
SQL query: Copy


PREPARE stmt FROM @query;
MySQL said: Documentation

#1044 - Access denied for user 'REDACTED'@'%' to database 'information_schema'

SHOW COLUMNS FROM `YOURPREFIX_threads` LIKE 'tyl_tnumtyls';

SHOW CREATE TABLE `YOURPREFIX_threads`;

(2023-08-22, 09:14 AM)Laird Wrote:

(2023-08-22, 08:21 AM)lost puppy Wrote: I tested it and all was well until the 5th query (creation of tyl_tnumtyls column) and the error given was:

Error
SQL query: Copy


PREPARE stmt FROM @query;
MySQL said: Documentation

#1044 - Access denied for user 'REDACTED'@'%' to database 'information_schema'

Do you also get an error when you run this (after replacing "YOURPREFIX" with your actual prefix)?

SHOW COLUMNS FROM `YOURPREFIX_threads` LIKE 'tyl_tnumtyls';

And is your threads table using the InnoDB engine? You can check by running (same replacement as previously):

SHOW CREATE TABLE `YOURPREFIX_threads`;

The last line of the output will include "ENGINE=[the engine type]".

 Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.

Your SQL query has been executed successfully.

Quote:InnoDB is a storage engine for the database management system MySQL and MariaDB. Since the release of MySQL 5.5.5 in 2010, it replaced MyISAM as MySQL's default table type.

(2023-08-22, 09:39 AM)lost puppy Wrote:

(2023-08-22, 09:14 AM)Laird Wrote: Do you also get an error when you run this (after replacing "YOURPREFIX" with your actual prefix)?

SHOW COLUMNS FROM `YOURPREFIX_threads` LIKE 'tyl_tnumtyls';

[...]

No error [...]

Quote:The same privileges apply to selecting information from INFORMATION_SCHEMA and viewing the same information through SHOW statements.

(2023-08-22, 09:39 AM)lost puppy Wrote: All the tables in the db are MyISAM

Quote:Some [INFORMATION_SCHEMA] tables have different privilege requirements; for these, the requirements are mentioned in the applicable table descriptions. For example, InnoDB tables (tables with names that begin with INNODB_) require the PROCESS privilege.