We’ve been getting lots of questions about how MySQL 5.1 optimises queries on partitioned tables. Recently, we added some new information about this to our Internals Manual provided by one of our developers, Sergey Petrunia. (I hope that my edits and formatting haven’t messed up anything — if there are any problems with what’s been published in the Internals Manual, then the fault’s probably mine and not his.)

The new section can be accessed here, and includes coverage of the following topics:

• An overview of how MySQL performs partition pruning
• What partitioning and subpartitioning intervals are, and why they’re important
• Converting WHERE and ON clauses into intervals
• Partition selection
• Where to find partition pruning and selection code in …

Yup.  It's time to start thinking about another MySQL Conference.  The Call for Participation is now up and I've been told that we will definitely have a Windows/.NET track again this year.  The Conference will be held at the same location as last year from April 23 to April 26, 2007.  Our conferences have grown every year and I expect this year to be no different, so get those proposals in before the November 7 deadline and hopefully I'll see you there!

Want to squeeze a bit more performance out of your MySQL install? Make sure you're not using swap!

Of course you were probably smart enough to make sure MySQL wasn't using all the systems memory to begin with so in theory you shouldn't be swapping. Good idea but the Linux VM isn't that clever.

It can still make poor decisions and decide to swap portions of MySQL out to disk which can end up hindering performance when it needs to page them back into memory. In replicated installs this can yield to periodic situations where certain MySQL slaves fall far behind and take 30 minutes or more to catch up with the master.

If you're not sure your MySQL install isn't using swap you can use vmstat to see realtime stats but I'd recommend using Munin or Ganglia as well.

What you …

A while back I did Cache Performance Comparison for LAMP Stack.

Looking at this data you can see memcached is about 5 times as slow as APC, and this is with tests done on localhost - with network difference is going to be larger, even with fastest network. Such latency can add up especially if you're, being lazy "P" Developer, request objects from cache one by one rather than fetching all items you need for the page at once (not to mention this is not always possible).

So I thought if there is any way to use both of them at once benefiting from strong sides of each of them.

APC Cache (Eaccelerator and other similar caches) is Fast but it is not distributed so you're wasting cache and reducing possible hit rate by caching things locally if you have many web servers. MemcacheD is relatively slow but distributed and …

When I was leaving MySQL I wrote I will be doing some Web projects on my own, besides doing MySQL Consulting. I think some people thought I am just using that as an excuse and really I will only do consulting. So I'm pretty happy to announce my first project.

NNSEEK Is NewsGroup search engine, as well as NewsGroup->Blog converter, allowing people to view NewsGroups as blog, meaning threads starts are showed as posts and replies are shown as comments.

It is quite early version so at this point - it has number of little problems, rough edges, it is also not overly optimized in terms of performance yet, but it is …

This Saturday, the 30th, at 4pm, I will be giving a session on MySQL Performance Tuning guidelines at the Ohio LinuxFest, at the Greater Columbus Convention Center. It is directly followed by the PostgreSQL session given by Christopher Browne, so if you're into databases and live in the Ohio area, you should have a bunch of geeks to bond with and share war stories. I'll actually be at the conference for most of the day. Come find me; I'll by wearing my MySQL shirt so I should be easy to find — I'll be challenging PostgreSQL guys to arm-wrestling bouts. Hmmm, forget that. If memory serves me correctly, at Marten's cookout this past April I fared pretty darn poorly in that arena. Best stick to lively debate.

As an aside, I've always …

I do not think you can use the result of a stored procedure in an ad-hoc subquery.

On my social network, LetsGetNuts.com, I have a Friend table. This is the structure:

mysql> describe Friend;
+—————-+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————-+—————+——+—–+———+——-+
| fkUser | int(11) | YES | MUL | NULL | |
| fkFriend | int(11) | YES | MUL | NULL | |
| confirmed | enum(’Y',’N') | YES | MUL | NULL | |
| confirmed_date | date | YES | | NULL | |
+—————-+—————+——+—–+———+——-+
4 rows in set (0.02 sec)

The issue here is that building a list of the friends of a certain user involves using a UNION, and I frequently need to use the concept of IN (FRIENDSLIST). I was …

I and Kostja will be in San Francisco next week. We have plans most of Tuesday but no plans for dinner on Tuesday night.

Anyone up for an impromptu dinner with a couple of MySQL'ers?

Either drop me some email or leave a message in comments. If I get a good response I will announce where we will go for dinner. Any good bars to recommend?

Following on my "the winning mentality" post, here's a nice little image that Paul Kedrosky clued me into. It shows where the Forbes 400 richest Americans live - the billionaires, that is....

Soon enough, I'm expecting to see one dot in Alabama (Digium....), another few in California (SugarCRM, MySQL, etc.), and heck, let's give North Carolina a few more dollars, too.

Open source will crack the billion-dollar barrier, and a lot of people and companies will see the benefit of it. Fortunately, the difference between wealth in open source and wealth in …

Today we have the great pleasure to announce the availability of the simplified Chinese version of our website. Many thanks to ZDYX for all his efforts to make this translation possible.

To the simplified Chinese translation