Mysql

MySQL performance tips from around the web

Gospel: use InnoDB, never MyISAM It seems everybody on StackExchange is singing from the same gospel: “[How can I] prevent queries from waiting for table level lock?” Answer: use InnoDB. The major advantages of InnoDB over MyISAM. “Even in a read-intesive system, just one DELETE or UPDATE statement will quickly nullify whatever benefits MyISAM has.” […] » about 400 words

Just catching on: MySQL supports tables in plain CSV

The  storage engine docs are quite clear — “the CSV storage engine stores data in text files using comma-separated values format” — and yet I never realized MySQL supported it.

Sure, the tables don’t support indexes and repairing them seems riskier than with other tables, but it still seems to offer a lot of convenience for some things. A comment in the docs suggests how easy CSV exports can be:

A comment suggests this super easy export to CSV approach:

CREATE TABLE csvtable SELECT * FROM innodbtable;
ALTER TABLE csvtable ENGINE=CSV;

That said, I really can’t imagine using it in production, or even as part of a repeated workflow.

Detect MySQL’s “too many connections” error

WordPress appears to continue with execution even when MySQL refuses connections/queries after init. Here’s a comment in the MySQL docs suggesting how to detect the condition in raw PHP:

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
if (mysql_errno() == 1203) {
  // 1203 == ER_TOO_MANY_USER_CONNECTIONS (mysqld_error.h)
  header("Location: http://your.site.com/alternate_page.php");
  exit;
}

Just a note to myself, but I wonder if there’s opportunity here.

Speeding up MySQL joins on tables with TEXT columns, maybe

The thing about WordPress’ DB schema is that TEXT and VARCHAR content is mixed in the posts table (to say nothing of the frustrations of DATETIME columns). That’s not such a problem for a blog with a few hundred posts, but it’s a different matter when you have a few hundred thousand posts. And it wouldn’t even […] » about 500 words

What is the difference utf8_unicode_ci and utf8_general_ci?

From the MySQL manual:

For any Unicode character set, operations performed using the xxx_general_ci collation are faster than those for the xxx_unicode_ci collation. For example, comparisons for the utf8_general_ci collation are faster, but slightly less correct, than comparisons for utf8_unicode_ci.

They have a amusing “examples of the effect of collation” set on “sorting German umlauts,” but it unhelpfully uses latin1_* collations. And another table that helpfully explains:

A difference between the collations is that this is true for utf8_general_ci:

ß = s

Whereas this is true for utf8_unicode_ci, which supports the German DIN-1 ordering (also known as dictionary order):

ß = ss

This forum post adds more info, but nowhere do they explain how a ☃ sorts against ☁ or ⛅.

How much faster is utf8_general_ci than utf8_unicode_ci, though? An August 2010 message in the MySQL forums seems to suggest the performance for specific operations could be 30% faster, but then dismisses the performance difference as unimportant compared to good indexing and writing efficient queries.

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):

UPDATE wp_8_postmeta
JOIN wp_8_posts ON wp_8_posts.ID = wp_8_postmeta.post_id
SET post_author = 15
WHERE meta_key = 'scrib_meditor_content'

Get the categories attached to every catalog record (except the “catalog” category):

SELECT tr.object_id , tr.term_taxonomy_id
FROM wp_8_term_relationships tr
JOIN wp_8_posts p ON p.ID = tr.object_id
WHERE tr.term_taxonomy_id IN (
	SELECT term_taxonomy_id
	FROM wp_8_term_taxonomy
	WHERE taxonomy = "category"
	AND term_id != 30
)
AND post_author = 15
ORDER BY tr.object_id , tr.term_taxonomy_id

Using the above list of object ids and term taxonomy ids, build a series of queries like the following to delete them:

DELETE FROM wp_8_term_relationships WHERE object_id = 12275 AND term_taxonomy_id = 271872 ;

Insert a catalog category relationship for all catalog records:

INSERT INTO wp_8_term_relationships
SELECT p.ID , '271871' , '0'
FROM wp_8_posts p
LEFT JOIN wp_8_term_relationships tr ON p.ID = tr.object_id AND tr.term_taxonomy_id = 271871
WHERE post_author = 15
AND tr.term_taxonomy_id IS NULL