Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#301 2015-09-28 19:04:00
Re: mem_postmaster - Postmaster Revamp
I have this error and – as usual – I have no idea how to solve it: we had to kill one of your MySql queries because it had been running for over 1 minute and it was impacting other users.
Query: SELECT COUNT(*) FROM (
SELECT S.subscriberID, S.subscriberFirstName, S.subscriberLastName, S.subscriberEmail,
GROUP_CONCAT(L.listName ORDER BY L.listName SEPARATOR ', ') as subscriberLists
FROM `bab_pm_subscribers` as S left join `bab_pm_subscribers_list` as MAP ON S.subscriberID=MAP.subscriber_id
left join `bab_pm_list_prefs` as L ON MAP.list_id=L.listID
WHERE 1
GROUP BY S.subscriberID) as T
WHERE 1
many thanks in advance.
ps. A hacked txp4.4.1 is used, because of this plugin, so any help would be greatly appreciated.
Last edited by colak (2015-09-28 19:20:43)
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
#302 2015-09-29 09:35:13
Re: mem_postmaster - Postmaster Revamp
anyone?
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
#303 2015-09-29 09:53:56
Re: mem_postmaster - Postmaster Revamp
Try executing that query from the command line or via PHPmyadmin and see how long it takes. If it’s far below 1 minute (and it probably is) and if you’re using webfaction, then see this topic
Offline
#304 2015-09-29 16:38:16
Re: mem_postmaster - Postmaster Revamp
There are indeed many rows in the bab_pm_subscribers table. But it is very slow indeed even in phpmyadmin I got no results after 3 minutes of waiting. I’m wondering if there is something wrong with the table. I’ll check it out. I am managing 2 sites with this plugin. One with just over 1000 subscribers and another with over 2500. The first seems just fine even though it is using a slightly older version of the plugin. The second, is just not working. Ruud, would it be OK if I send you a l/p for the site for you to check?
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
#305 2015-09-29 16:52:46
Re: mem_postmaster - Postmaster Revamp
Sure, go ahead.
Offline
#306 2015-09-29 16:59:51
Re: mem_postmaster - Postmaster Revamp
Thanks so much ruud. You have mail!
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
#307 2015-09-29 17:26:57
Re: mem_postmaster - Postmaster Revamp
ps. Would you also like the server and mysql logins?
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
#308 2015-09-29 18:49:21
Re: mem_postmaster - Postmaster Revamp
Interesting. That query sometimes takes a few seconds to complete, but at other times it just keeps going and results in an error. This is not normal and probably means that the SQL server is not performing consistently.
I wonder why this plugin used a huge query:
SELECT COUNT(*) FROM (
SELECT S.subscriberID, S.subscriberFirstName, S.subscriberLastName, S.subscriberEmail,
GROUP_CONCAT(L.listName ORDER BY L.listName SEPARATOR ', ') as subscriberLists
FROM `bab_pm_subscribers` as S
left join `bab_pm_subscribers_list` as MAP ON S.subscriberID=MAP.subscriber_id
left join `bab_pm_list_prefs` as L ON MAP.list_id=L.listID
WHERE 1
GROUP BY S.subscriberID
) as T
WHERE 1
… because this gives the exact same result, but is much much faster:
SELECT COUNT(*) FROM `bab_pm_subscribers`
Ah, I see. The “WHERE” part is not always simply “1”.
You can optimize the queries when the WHERE part is 1, but that doesn’t solve the problem, because I see there’s a very similar, but heavier query (because it doesn’t just count, but returns a large table) just below that in the plugin code and that one cannot be simplified, so the question remains: why does that querie sometimes execute within seconds (or less) and at other times take more than a minute. That’s something your webhost should answer.
Offline
#309 2015-09-30 06:08:07
Re: mem_postmaster - Postmaster Revamp
Thanks so much Ruud. I wrote to the host. Hopefully they will respond soon.
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
#310 2015-09-30 17:35:18
Re: mem_postmaster - Postmaster Revamp
Would it break postmaster, txp or the internet if I use indexes on the bab_pm_subscribers table? Namely on S.subscriberID, S.subscriberFirstName, S.subscriberLastName, S.subscriberEmail
columns (or is it rows?)
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
#311 2015-09-30 18:31:22
Re: mem_postmaster - Postmaster Revamp
Adding indexes never breaks anything. You add indexes on columns (can be more than one per index).
It makes most sense to add them on columns that are used to select data and combine tables:
- bab_pm_subscribers => subscriberID
- bab_pm_subscribers_list => subscriber_id and list_id (one index for each).
- bab_pm_list_prefs => listID
I don’t think this will solve your problem, but it can’t hurt to try.
Offline
#312 2015-10-01 05:23:09
Re: mem_postmaster - Postmaster Revamp
Thanks so much Ruud, I added them and the speed has multiplied although it might be the time of day.
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline