Tag Archives: mysql

Otto on Caching in WordPress

Otto describes why neither page caching, nor persistent object caching are part of the WordPress core, and why they probably never will be. The main reasons are the way WordPress is architected, and the way it is used by the majority.

If you’re a person writing a blog that gets less than 1000 hits a day, caching ain’t going to do much for you.

And my favorite, about why object cache doesn’t (and probably shouldn’t) use the database for persistent storage:

Trust the MySQL query optimizer, it’s smarter than you are.

Change the year of all posts in a particular category to 2012

Snippet! Change the year of all posts in a particular category to 2012 with a single SQL query (use with phpMyAdmin or the MySQL command line interface)

UPDATE wp_posts AS p
  JOIN wp_term_relationships AS tr ON tr.object_id = p.id
  JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
  JOIN wp_terms AS t ON tt.term_id = t.term_id
  SET p.post_date = REPLACE(p.post_date, YEAR(p.post_date), 2012)
  WHERE t.slug = 'my-category-slug' AND tt.taxonomy = 'category';

Obviously replace my-category-slug with the category you’d like to target and perform the same magic on the p.post_date_gmt field. Not entirely sure why you would use such a technique, but it was asked by Jim Edwards on Twitter.

MySQL Index Hinting

Did you know that you can give a hint to MySQL on which index to use? It’s called Index Hint and can be part of a query where you feel MySQL is doing a wrong choice (although that’s quite unlikely these days.)

SELECT * FROM table1 USE INDEX (your_index)
    WHERE col1 = 1 AND col2 = 2 AND col3 = 3;

Where your_index is the name of the index you’d like to use for this query. You can supply several comma-separated indexes and MySQL will pick the one it thinks is best. Alternatively you can tell MySQL to IGNORE INDEX too! Do benchmark though before making the final decision ;)

Encode Entities Inside PRE Tags

Here’s a little Python script that searches through a given file for pre tags and encodes anything in between. This is useful for when escaping from syntax highlighting plugins and replacing every occurrence of the code shortcode with a pre tag.

import re, sys

# First argument is the filename, output is filename.encoded
filename = sys.argv[1]
f = file(filename)
output = open('%s.encoded' % filename, 'w+');

# Read the whole file, fire the regular expressions
contents = f.read()
expr = re.compile(r'<pre>(.*?)</pre>', re.MULTILINE|re.DOTALL)
matches = expr.findall(contents)

# Loop through each match and replace < > with &lt; and &gt;
for match in matches:
	contents = contents.replace(match, match.replace('<', '&lt;').replace('>', '&gt;'));

# Write output file and close both files
output.write(contents)
output.close()
f.close()

Most syntax highlighting plugins will encode all entities on the fly for you so when you stop using them your code might break. Also, most highlighting plugins will render your TinyMCE visual editor useless when working with code, and I think it’s quite common to work with code using the visual editor in WordPress. At least Twenty Ten and Twenty Eleven understand that ;)

However, as seen from the replacement part, I don’t really encode all entities but rather replace the greater than and less than symbols. It’s enough for most cases but if you need a real entity encoding you should use the cgi.escape function which is similar to htmlspecialchars in php.

Feed this script with your database dump and it’ll create a new file with an .encoded prefix which you can feed back to MySQL. Please note though that this script reads the entier input file which may lead to slow execution, high memory usage and swapping when working with large files. Worked fine on my 30 megabyte database though.

Regex Replace in MySQL or lib_mysqludf_preg in Ubuntu Linux

I’ve been working a lot with MySQL lately, especially after the major theme and plugin upgrades on my blog. I was dealing with a bunch of content issues like redundant shortcodes and post meta, URL changes, images directories and more.

One simple solution would be to grab the database dump, perform various search and replace operations and then feed it back in, and my goal was to do that without data loss, without going offline, without sending files back and forth but mainly for learning purposes.

I found a UDF for MySQL called lib_mysqludf_preg and here’s how I got it to run on Ubuntu 11.04, somewhere in a temporary directory:

sudo apt-get install libpcre3-dev libmysqlclient-dev
wget http://www.mysqludf.org/lib_mysqludf_preg/lib_mysqludf_preg-1.0.1.tar.gz
tar -xf lib_mysqludf_preg-1.0.1.tar.gz
cd lib_mysqludf_preg-1.0.1
sudo ./configure
sudo make install
sudo service mysql restart

So, install some libraries upon which the UDF may depend, download the UDF archive, extract it, browse to what has been extracted, configure, install and restart MySQL (just in case.) If you’re reading this and it’s not 2011, make sure you get the latest version and read the release notes too.

At this point the UDF library is installed but the functions are not available yet, so log in to your MySQL command line, preferably as root and create your new preg_replace function like this:

CREATE FUNCTION preg_replace RETURNS STRING SONAME 'lib_mysqludf_preg.so';

Voila! And here’s how I removed a shortcode from all posts and pages:

UPDATE wp_posts
  SET post_content = PREG_REPLACE('/[my_shortcode*?.*?]/', '', post_content)
  WHERE post_content LIKE '%[my_shortcode%';

Make sure you backup your database first though before doing any of this. I did! But everything went fine. My next goal is to get the shortcode provided by a syntax highlighting plugin replaced with a <pre> tag. Hopefully I won’t break anything so good luck to me and thank you so much for stopping by!

Unreplied Comments in WordPress

Dealing with comments. What a mess! I’ve been quite busy lately so I hadn’t had too much time to reply to each and every comment on my blog, but I’d really love too, seriously! The problem however is that there were times when I replied to somebody, and times when I hadn’t and now with this mess in my comments admin it’s impossible to find out ones I haven’t replied to.

Comments: Such a mess!

I was looking for a plugin but haven’t found anything good enough. Even hosted commenting services like Disqus and Livefyre seem to lack that. Ideally I’d like to work with comments like with e-mail — read them, mark as unread, flag, assign to somebody, etc. So I quickly drafted an SQL query that gave me a list of IDs of comments that have not been replied to excluding my own:

SELECT t1.comment_ID FROM wp_comments AS t1
    LEFT JOIN wp_comments AS t2 ON t2.comment_parent = t1.comment_ID
    WHERE t2.comment_ID IS NULL
    AND t1.user_id = 0
    AND (t1.comment_approved = '0' OR t1.comment_approved = '1')
    ORDER BY t1.comment_date_gmt;

I was shocked by the amount of rows it returned so I switched the “disable commenting on posts older than 14 days” checkbox to make sure that doesn’t happen again. You can always reach me on Twitter or e-mail, right? ;)

I’d love to see this as a beginning for a new WordPress plugin that would implement some goodies for handling comments, or maybe as a tip to the hosted commenting services to create new features. In any case, I’m now stuck with over a thousand comments I have to go through.