db

I have over 1000 posts here at MaisonBisson, but even so, the table with all those posts is under 3MB. Now I’ve got a project with 150,000 posts — yes, 150,000 posts! — and the table is about 500MB. An associated table, structured sort of like WP’s postsmeta, has over 1.5 million records and weighs in at over 100MB (not including the 150MB of indexes).

Up to now I’ve been a “throw more hardware at it” sort of guy — and in a server with only 1GB of RAM, that’s probably the best solution — but I also think it’s time I learned some MySQL optimization tricks. Zach‘s been pushing me to get mytop installed for quite a while, and I finally got around to it.

mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI, and Term::ReadKey installed. And with Term::ANSIColor installed you even get color. If you install Time::HiRes, you’ll get good real-time queries/second stats.

I’m going out on a limb to say MySQL’s full-text indexing and searching features are underused. They appeared in MySQL 3.23.23 (most people are using 4.x, and 5 is in development), but it’s been news to most of the people I know.

Here’s the deal, the MATCH() function can search a full-text index for a string of text (one or more words) and return relevance-ranked results. It’s at the core of the list of related links at the bottom of every post here.

For that query, I put all the tag names into a single variable that might look like this:

$keywords = “mysql database php select full-text search full-text searching docs documentation”

Then I do a select that looks something like this:

SELECT * FROM wp_posts WHERE MATCH(post_title,post_content) AGAINST(‘$keywords’);

The docs give a lot more detail, including how to do boolean searches.