photo: coaster
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.
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 lot [...]
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.
# mysqlcheck -p -A –auto-repair –optimize
wp_1_options
info : Found block with too small length at 17732; Skipped
info : Wrong block with wrong total length starting at 17776
info : Found block with too small length at 28776; Skipped
warning : Number of rows changed from [...]
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. [...]
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 the [...]
Google turned this up, but i have no idea how old it is: How to Monitor MySQL’s performance.
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 FROM mysql.user_copy [...]
When doing a bulk insert/update/change to a MySQL table you can temporarily disable index updates like this:
ALTER TABLE $tbl_name DISABLE KEYS
…do stuff…
ALTER TABLE $tbl_name ENABLE KEYS
From the docs:
ALTER TABLE … DISABLE KEYS tells MySQL to stop updating non-unique indexes. ALTER TABLE … ENABLE KEYS then should be used to re-create missing indexes. MySQL does this [...]
After an upgrade to MySQL 5.0.51b on RHEL 5 I started seeing curious results in a fairly common query. Here’s a simplified version:
SELECT ID, post_date_gmt
FROM wp_posts
GROUP BY ID
ORDER BY post_date_gmt DESC
LIMIT 5
What I expected was to get a handful of post ID numbers sorted in descending order by the post_date_gmt. Instead, I got [...]
