Alex Bluesummers asked on a WordPress list: How do I order posts in the loop by whether or not it is in a category, then by date? Suppose I have 10 posts, of which 5 are in the category “Sports” and 5 are in the category “Blog News”. Both “Sports” and “Blog News” posts are [...]
April 27, 2010
SSD MySQL Performance
The above graph and this MySQL performance blog story are from last year, but I believe are still relevant and instructive now. Sure, the FusionIO is faster, but how the hell can you beat a single SSD in terms of price/performance? RAID 10: 4.8 transactions per minute per dollar SSD: 27 transactions per minute per [...]
April 26, 2010
Improving P2 — Order Posts By Last Comment Date
I’m a big fan of the P2 theme for WordPress. It makes it dead easy anybody familiar with WordPress to host a discussion site and improve collaboration across time and distance. That said, one feature I’d like to see is the ability to order the posts by the last comment date, rather than post date. [...]
April 18, 2010
Cleaning Up Category Relationships In A WordPress Scriblio Site
A few lines of SQL I used to clean up a Scriblio site. It’s probably useless to anybody but me. I’m not suggesting anybody else use this code, as it will result in changed or deleted data. Update the post author for catalog records (identified because they have a specific post meta entry): Get the [...]
April 15, 2010
A Few Lines of SQL: Cloning Blogs In MU
The following SQL is what I used to clone the content from one blog in MU to another for testing. It’s probably useless to anybody but me. Anybody who can’t figure out from the code that wp_8_posts is the source table and wp_13_posts is the destination probably shouldn’t try to use the code, as data [...]
May 16, 2009
Is MySQL 5.1 Ready?
MySQL 5.1 hasn’t gotten a lot of love, but it does introduce support for pluggable storage engines. And that’s required to use SphinxSE. Sphinx is a fast full text search engine. It doesn’t need to run as a MySQL storage engine to work, but doing that allows joining against other MySQL tables. So while I’m [...]
May 11, 2009
The Difference Between MySQL’s utf8_unicode_ci and. utf8_general_ci Collations
MySQL answer: utf8_unicode_ci vs. utf8_general_ci. Collation controls sorting behavior. Unicode rationalizes the character set, but doesn’t, on it’s own, rationalize sorting behavior for all the various languages it supports. utf8_general_ci (ci = case insensitive) is apparently a bit faster, but sloppier, and only appropriate for English language data sets.
March 17, 2009
MySQL Slow Query Log Analysis
Peter at MySQL Performance Blog pointed out this sweet perl script to analyze MySQL’s slow query logs. (This is supposedly a PHP port.) The script does a good job of aggregating similar queries (those that only differ in their query values) and displaying overall stats for them. The following two queries are showing up a [...]
February 19, 2009
5,848 (max), 656 (avg) MySQL Queries Per Second
The above graph is far from typical, but I love that the box (the top one in this picture) can do the job when it needs to. This activity is a result of bulk record imports, web activity results in relatively little database traffic due to my use of Memcached and Batcache.
December 10, 2008
MySQL 5.1 Released, Community Takes Stock
MySQL 5.1 is out as a GA release, but with crashing bugs that should give likely users pause. Perhaps worse, the problems are blamed on essential breakdowns in the project management: “We have changed the release model so that instead of focusing on quality and features our release is now defined by timeliness and features. [...]
October 9, 2008
Converting MySQL Character Sets
This Gentoo Wiki page suggests dumping the table and using iconv to convert the characters, then insert the dump into a new table with the new charset. Alex King solved a different problem: his apps were talking UTF8, but his tables were Latin1. His solution was to dump the tables, change the charset info in [...]
August 24, 2008
MySQL Performance Monitoring Tips From The MySQL Newsletter
August 14, 2008
Copying MySQL Usernames and Database Priveleges
Now that I’m the nominal MySQL DBA for PSU, it became my job to jimmy up the MySQL user privileges so that the new web server could connect. I’m not sure if this is the fastest, most efficient way to do it, but it worked quickly enough: CREATE TABLE mysql.user_copy SELECT * FROM mysql.user; DELETE [...]
Not true
Posted in Technology | Leave a Comment »