Just yesterday I sent my first newsletter using Simplenews and for now all work wonderful.

I sent more than 10k emails, 500 per hour in a cron job.

But I have a few questions:

The statistics only appear when all the emails was sent?

The open rate is growing quickly, just now 20%, but to open the statistics page is really really slow, take more than 1 min to open. This is normal?

I'll send a newsletter every week, and the suscripters are now ~20k. With a second newsletter the statistics page will be more slow?

Excelent work!

CommentFileSizeAuthor
#8 split_query.patch2.11 KBderjochenmeyer

Comments

Docc’s picture

Docc commented

The statistics only appear when all the emails was sent?
yes

You mean the main statistics page "admin/content/simplenews/statistics" that is slow?
ill have to reproduce that. Never had a case with 10k or more subscribers.
Ill get back to you when i had a change to test it.

RAFA3L’s picture

RAFA3L commented

Yes, "admin/content/simplenews/statistics" is slow.

I notice that you run one only query to call the data:

$data = db_fetch_object(db_query("SELECT
																		 	  COUNT(DISTINCT v.email) AS opens,
																				COUNT(DISTINCT c.email) AS clicks
																			FROM
																			  {simplenews_statistics_opens} v
																			LEFT JOIN
																			  {simplenews_statistics_clicks} c
																			ON
																			  v.nid = c.nid
																			WHERE
																			  v.nid = %d", $node->nid));

I don't know, but maybe you can tray making two queries, one for open rate an another to ctr. And why you compare v.nid = c.nid ?

I have a similar query:

$sql = 'SELECT COUNT( A.ipaddr ) FROM ( SELECT ipaddr FROM opentracking GROUP BY ipaddr ) A';

And run it more than 20 times in a big database and is fast.

RAFA3L’s picture

RAFA3L commented

Ok,

I make a few tests with this queries using phpmyadmin:

SELECT COUNT( A.email ) FROM ( SELECT email FROM simplenews_statistics_clicks WHERE nid=93 GROUP BY email ) A

SELECT COUNT( A.email ) FROM ( SELECT email FROM simplenews_statistics_opens WHERE nid=93 GROUP BY email ) A

Both run pretty fast and with the same result that show the Statistics page.

Docc’s picture

Docc commented
Assigned: Unassigned » Docc
Status: Active » Needs work

changin status so i wont forget ;)

Valeratal’s picture

Valeratal commented

i have this trouble too

admin/content/simplenews/statistics - very slow

derjochenmeyer’s picture

derjochenmeyer commented

Devel Module logs this:

6191.05ms
simplenews_statistics_admin_form
SELECT COUNT(DISTINCT v.email) AS opens, COUNT(DISTINCT c.email) AS clicks FROM simplenews_statistics_opens v LEFT JOIN simplenews_statistics_clicks c ON v.nid = c.nid WHERE v.nid = 4658

6131.36ms
simplenews_statistics_admin_form
SELECT COUNT(DISTINCT v.email) AS opens, COUNT(DISTINCT c.email) AS clicks FROM simplenews_statistics_opens v LEFT JOIN simplenews_statistics_clicks c ON v.nid = c.nid WHERE v.nid = 4733

6010.46ms
simplenews_statistics_admin_form
SELECT COUNT(DISTINCT v.email) AS opens, COUNT(DISTINCT c.email) AS clicks FROM simplenews_statistics_opens v LEFT JOIN simplenews_statistics_clicks c ON v.nid = c.nid WHERE v.nid = 4708

5612.34ms
simplenews_statistics_admin_form
SELECT COUNT(DISTINCT v.email) AS opens, COUNT(DISTINCT c.email) AS clicks FROM simplenews_statistics_opens v LEFT JOIN simplenews_statistics_clicks c ON v.nid = c.nid WHERE v.nid = 4751

5241.32ms
simplenews_statistics_admin_form
SELECT COUNT(DISTINCT v.email) AS opens, COUNT(DISTINCT c.email) AS clicks FROM simplenews_statistics_opens v LEFT JOIN simplenews_statistics_clicks c ON v.nid = c.nid WHERE v.nid = 4768

derjochenmeyer’s picture

derjochenmeyer commented
Status: Needs work » Needs review

I checked the Database. Both Tables simplenews_statistics_opens AND simplenews_statistics_clicks have NO INDEX at all. Adding an index on nid increases the performance of this query dramatically.

6286.52ms >> with index >> 271.03ms
6246.97ms >> with index >> 196.88ms
6186.95ms >> with index >> 168.52ms
5656.12ms >> with index >> 108.13ms
3082.33ms >> with index >> 38.15ms

derjochenmeyer’s picture

derjochenmeyer commented
StatusFileSize
new split_query.patch2.11 KB

Splitting the query and removing the INNER JOIN further increases performance dreamatically ;>. Example: The query that took 271.03ms to execute (6286.52ms without INDEX) runs now in less than 4ms.

This query is run for each (!) sent newsletter.
So the real benefit is 6286.52ms * number_of_newsletters vs. 4ms * number_of_newsletters.

Docc’s picture

Docc commented

guys a new 2.0 branche is coming up wich fixes this issue.

Docc’s picture

Docc commented
Version: 6.x-1.2 » 6.x-2.1
Status: Needs review » Fixed

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.