In my previous post about optimizing the comment relish plugin I managed to lower the load generated by the comment relish plugin on the database server by adding an index on a column in the cr_emailed table and by optimizing a query ( basically removing a "useless?" join ). This improved the load time a lot but some users still reported slow load times on blogs with a lot of comments.
Looking more over the source code I realized that the function that tried to find new commentators was executed on each page. This function was executing a mysql query that joined two tables wp_comments and wp_cr_emailed. I think you can imagine the result of this on blogs with a large number of comments.
The solution was to execute this function only when a new comment was posted. And this comes with two benefits:
- because this is called only when a new comment is posted ( or approved ) the rest of the blog will remain as fast as before the plugin was installed.
- because of the way we call the function ( as an action/hook associated with the code that processes the comments ) we have more information about the comment so we don't have to do the query where we join two tables. We still do one query to get the whole comment data and one to see if this is a new commentator ( not emailed yet ) but these use indexes and are really fast.
Get the new optimized plugin here and let me know how it works for you.
Update:
the plugin link above contains a plugin that tries to send email even when the blog receives a pingback or trackback as reported by Rhys. I have uploaded another version that corrects this problem here:Â [download id="9" format="1"]
Thank you VERY MUCH! I was going nuts!
Thanks for creating this, I’ve been waiting for this 🙂
I’m assuming with the two pingbacks that pingbacks work fine with this plugin (unless you haven’t installed it here) 😉
Actually I had the older version installed here, the one I presented in this post:
http://patchlog.com/wordpress/comment-relish-optimization/ and I didn’t notice this problem.
Anyway here’s an update that does not try to send an email to pingbacks and trackbacks http://patchlog.com/wp-content/plugins/comment-relish.new.3.zip
Thank you for this, I guess I should’ve explained myself better.
On my old theme, and the old version of comment relish, for reasons you identified in that post, my blog would crash if somebody sent me a trackback. As a result, I managed to edit it so that trackbacks didn’t activate the code like comments did.
Excellent work by the way!
Love the plugin. Now I just need to create an interesting message 😉
I’m still getting the error and can’t seem to fix it. I downloaded the new one, delete the old one – do I need to reinstall and deactivate first? help!
What error are you getting exactly ?
Very nice patch (and much needed, jeez, looking for new commenters on each page load?? silliest stuff ever)
A few more stuff to fix for you!
– It still misses some stripslashes (on line 288 and when outputting the options in the admin page, otherwise you see plenty of “I\’m happy you\’ve left a comment”
– it’s full of PHP short tags (<? instead of <?php) which will make the plugin break everything on purist setups.
Thanks for the suggestions. I will release another patch that fixes the stripslashes thing. As for the short tags, I never encountered such a problem.
your blog ate my comment :/ (contained some other stuff to fix)
nope, I just have to approve new comentators
Comment Relish was working great, until today when it crashed my blog when I accidentally made a trackback from one post to another.
I’m assuming with the two pingbacks that pingbacks work fine with this plugin (unless you haven’t installed it here) 😉
Everything’s cool, but your plugin doesn’t support any letters from non-english charset. 🙁
Well it’s not my plugin. I just fixed one issue with it.
So what stops you from adding some features?
mostly lack of free time
So installed v3 on a test instance of my site and left 4 comments. I got 4 welcome messages – anyone experienced this as well?
cheers,
Zac
Same issue as with Zac above.
v3 on my WordPress 2.6 sends welcome email to each comment I leave. Can you guys look into it?
The problem is most likely because the plugin was not able to create the cr_emailed table. deactivate the plugin and ( If you can access wordpress database ) lookup in wp_options for cr_installed and set it to ‘false’ and then just reactivate the plugin. This should just create the table : cr_emailed. look for it in the database to make sure it’s there. If it’s there then you should not have that problem anymore.
I was having the same problem as Zac, and I couldn’t get the plugin to create the table, so I just manually created it.
Just replace the wp in wp_cr_emailed if you have a different wordpress prefix.
CREATE TABLE wp_cr_emailed (
emailed_ID mediumint(9) NOT NULL AUTO_INCREMENT,
time bigint(11) DEFAULT ‘0’ NOT NULL,
email varchar(255) NOT NULL,
UNIQUE KEY emailed_ID (emailed_ID),
INDEX ( email )
);
great article. thanks for posting the optimization tips.
I got a question though. Would it be a hard thing to do to add a delay into the email that relish sends out? Ive noticed that when someone new leaves a comment on my site, the welcome email I set up is sent out to them immediately. I would like to be able to put a delay of maybe 2 or 5 mins before the email is sent out.
thanks
You could do that if you would create a system to send emails in batches … right now the plugin hooks into the comment posting function so it would be unacceptable to make the user wait for 2-5 minutes before his comment is posted.
So have your fixed all the bugs or is anyone experiencing more problems with this? If so, is there another available amended version of this plugin somewhere else that works correctly? Thanks for your help.
The only problem that might exist but may now show up for everybody is that the plugin doesn’t create the database when you install it.
You can check your wp db after you install it. If you see a table named {WP_PREFIX}_cr_emailed then it’s ok.If not then you should create the db manually by running this sql in a mysql client like phpmyadmin or whatever you use:
CREATE TABLE {WP_PREFIX}_cr_emailed (
emailed_ID mediumint(9) NOT NULL AUTO_INCREMENT,
time bigint(11) DEFAULT 0 NOT NULL,
email varchar(255) NOT NULL,
UNIQUE KEY emailed_ID (emailed_ID),
INDEX ( email )
);
WP_PREFIX is the prefix that all wordpress tables have. By default this is ‘wp’
i tryed to create the db manually with the code:
CREATE TABLE _cr_emailed (
emailed_ID mediumint(9) NOT NULL AUTO_INCREMENT,
time bigint(11) DEFAULT ‘0′ NOT NULL,
email varchar(255) NOT NULL,
UNIQUE KEY emailed_ID (emailed_ID),
INDEX ( email )
);
but the response is:
query SQL :
CREATE TABLE _cr_emailed ( emailed_ID mediumint(9) NOT NULL AUTO_INCREMENT, time bigint(11) DEFAULT ‘0&email varchar(255) NOT NULL, UNIQUE KEY emailed_ID (emailed_ID), INDEX ( email ) )
Messaggio di MySQL:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘0&email varchar(255) NOT NULL,
UNIQUE KEY emailed_ID (emailed_ID),
INDEX ( emai’ at line 3
How can I fix this?
THANKS
My comment with indications about how to create the table was wrong. I used tags in it and didn’t realize wordpress would interpret the tags.
Also this line:
time bigint(11) DEFAULT ‘0′ NOT NULL,
should have been simply:
time bigint(11) DEFAULT 0 NOT NULL,
Anyway please take a look again at the corrected comment, right above yours and try again.
Is this no longer available? The links don’t work
Glen Allsopp’s last blog post..14 Things I Strive to Do in this Lifetime
I just updated the link to the latest optimized version. the rest are not updated because I don’t want to encourage anyone to download broken versions.
Thanks for the notice.