Comment Relish is a wordpress plugin that sends an email the first time someone comments on your blog. The plugin allows you to define the message in wp-admin and you can embed tags like author's name, email, website and others in the message.
This can be used to send a welcome message to the first time commentators and maybe invite them to subscribe to your rss feed.
The Problem
John Chow, tried to use it on his blog but the plugin and his huge number of comments ( 43000 ? ) put the site down for about 2 hours. He said the plugin misbehaved and he would not recommend it if you have a lot of comments.
I did a little simulation on a wordpres blog with about 25000 comments and I can see how the plugin misbehaves :). It took more then tho minutes to select the email addresses that should be emailed before I decided to stop it, because that's just unacceptable, but I think it would have taken a lot more.
I managed to optimize it easily by just modifying a field in the table used by this plugin. If you don't care how I did it and just want the optimized plugin skip to the end of the post. If not then read on...
How it works
The plugin uses a table ( cr_emailed ) to remember which addresses received an email, so that it does not send a message more then once. Basically the plugin just inserts a new record in this table every time someone new comments on the blog. It tries to see if someone is new by doing a LEFT JOIN select on two tables: comments and cr_emailed, and then filters the results that have cr_emailed.email = NULL. The problem with this is that the email column has no index so if you have 43000 comments the query will have a huge result set and mysql will have to process each result to find out if cr_emailed.email is null ( the email has not received a message yet ) .
The solution
This was really simple, just convert the email field to a varchar ( needed in order to be able to set a fixed length key on this field ) and then add an index on it.
After doing this the query that took more then two minutes, now takes a little less then 1 second.
Some one on John Chow's blog suggested to add an index to wp_comments.comment_author_email. I tried that but I noticed no improvements, and using explain on the sql query shows the index on comment_author_email is not used, so that's useless.
Looking more over the code I noticed another weird thing. The query executed to find the email addresses looks like this:
I wonder why the author used the posts table in this query? The results from the posts table are not used anywhere in the code. I really couldn't see a good reason for keeping the posts table in this query so I removed the posts table from this query, making it even faster.
Get the source code
Here is a diff file between my version and the original version and here is a my new version . If you have this plugin already installed you have to uninstall it and remove the wp_cr_emailed table and then copy the new version and activate it in wp-admin.
I'm using the new version on this blog and is working but I have few comments here. I have only tested the modifications on this blog and on a fictional blog with 25000 automatically generated comments so the usual disclaimer applies... you know all that "WARNING NO WARRANTIES" stuff...
If you have a blog with many comments and you want to try this, let me know how it works in a real environment.
Thanks for this, on my 12,000 comment site the original plugin just killed the site, made it inaccessible. It also sent the thankyou email NINE times to the test comment I made.
Did you try the modified plugin? How did it do ?
Yes, well it searched through 12,000 comments and found the first instance of each commenters’ email and sent them the “Thanks” message. Argggh! No-one has complained yet, although I got a few quizzical replies – like “I wrote that comment one year ago”, etc.
that’s not how it’s supposed to work. The first time you install /activate it should get all unique email addresses from your comments and insert them into a table named wp_cr_emailed . This will ensure those that commented before this plugin was activated will not receive any message.
Well i cant get the plugin to work.
It doesnt send any e-mails.
Help me if you want me to help you :). If you just tell me you can’t get it to work that’s not helping anyone. you might think giving some details…maybe, did you enabled the plugin after you activated it? can you look into the database to see if it created the wp_cr_emailed table? Does emailing from php ( and wordpress ) even work on your hosting account ? any other information you think it may be relevant…
Ok.
– I Enabled the plugin after activated
– wp_cr_emailed table was created successfully
I’m not sure about the emailing with php thing, but i think it does because i have a contact form and works perfectly.
I did the obvious steps, but i tried and tried and couldnt get it work.
Anyways, dont mind.
I’ts a great idea but it need tweaking, because when i activate it my site is like 2x slower than it is when plugin is desactivated.
Hope to find better versions in the future 😉
I’m running this on this blog and it seems to work. Btw did you get the “thanks for commenting” message from me when you first commented here?
You are right about one thing. IT is possible that this plugin will still make your blog slower. This is because the function that sends the email messages is called on each page load. It should not work like this. It should only try to send the email messages when someone is adding a new comment or when a comment is set as approved.
I will make the modifications required and will post them in a few days.
– Yes i received your e-mail.
– Yes you are right again, the problem is that the function is called on each page, even on administration panel.
I knew something was wrong because i got 2 domains on same hosting and one was loading like twice as faster as the other, only disabled comment relish and worked fine.
– btw, my table was even written with some emails.
Hmm, I’m also having this problem.
— php mail() seems to be working fine on my server — WordPress emailing me works fine, and my contact form works fine. Subscribe to Comments also works fine.
— wp_cr_emailed was created successfully, however, I did post a test comment (from a different email I’ve never posted a comment with before), and the table is empty.
— I have accessed my site from an anonymous (not logged in) user, AND my admin account. Neither of which triggered the email to send.
— Plugin is activated, AND enabled.
Any ideas?
Here’s quick question…
I de-activated this plugin due to it MIS-BEHAVING…
Is it safe to delete this table from the database? wp_crp_emailed?
I want to clean up any unused tables from the DB.
What do you think?
Thanks.
I don’t see any problems with deleting wp_cr_emailed if your plugin is deactivated.
Hey thanks for the mods, I’ve been out of pocket for a while, I heard of some issues but hadn’t had the opportunity to correct the plugin. Thanks again,
You’re welcome! 🙂
Thanks, Mihai. Your efforts are appreciated. I am looking forward to employing the plugin.
Ich depp: Problem vom blogtopf.de gefunden: http://tinyurl.com/4of4cm mal schauen wie ich das nun löse.
Hi! I'm using comment relish and really like it, but its sending me a response every time I comment on my own bog, so I am wondering if it doing that with visitors as well. It doesn't seem to recognize that I've been there before!
I am new to code, and my blog is in PHP, which makes it harder since the little I do know is html. Do you have nay suggestions?
if it's sending to you it will probabbly do the same for your visitors.
You have to check is there is a table named wp_cr_emailed in your wordpress database
If there isn't you'll have to create it … read the comments on this post http://patchlog.com/wordpress/more-optimization… to find out how to create it
well where is the new Version?
TechPaparazzi’s last blog post..Dell Adamo,The MacBook Air Rival-Brief Review.