🕸Fergus Duniho wrote on Fri, Feb 17, 2023 07:15 PM UTC:
I modified the script to count comments and revisions with a single query for each table at the beginning, but it was still going slowly, because it also does queries for each item to find out the last action. To address that, I modified the one-time queries to also include information on the latest commentdate or revisiontime. But I'm not sure how to modify it to also include who wrote the comment or made the revision. So, it is still doing additional queries while listing the output. But instead of doing two queries for each item, is is now doing only one. Also, I was able to simplify each of those queries by including the commentdate or revisiontime from the one-time queries. So, altogether, it was previously doing four additional queries per item, and now it is doing only one additional query, and each of those should take less time now, which should speed it up, but it's still listing items in spurts.
Additionally, it is also checking whether there is an entry in MemberSubmissions, which takes another query. So, it's actually doing two additional queries per item. For items created after the Revisions tables, I might be able to use the revisions count to tell whether the page has been created yet, which would save the need to query MemberSubmissions. Certainly, if there are any revisions, the page has already been created, and I could query MemberSubmissions only for items with no revisions. So, it will now query the MemberSubmissions table only when the revision count is zero, which should shave off some more time.
I modified the script to count comments and revisions with a single query for each table at the beginning, but it was still going slowly, because it also does queries for each item to find out the last action. To address that, I modified the one-time queries to also include information on the latest commentdate or revisiontime. But I'm not sure how to modify it to also include who wrote the comment or made the revision. So, it is still doing additional queries while listing the output. But instead of doing two queries for each item, is is now doing only one. Also, I was able to simplify each of those queries by including the commentdate or revisiontime from the one-time queries. So, altogether, it was previously doing four additional queries per item, and now it is doing only one additional query, and each of those should take less time now, which should speed it up, but it's still listing items in spurts.
Additionally, it is also checking whether there is an entry in MemberSubmissions, which takes another query. So, it's actually doing two additional queries per item. For items created after the Revisions tables, I might be able to use the revisions count to tell whether the page has been created yet, which would save the need to query MemberSubmissions. Certainly, if there are any revisions, the page has already been created, and I could query MemberSubmissions only for items with no revisions. So, it will now query the MemberSubmissions table only when the revision count is zero, which should shave off some more time.