MySQL 5.1 optimizer not using index

[John Feuerstein]

Binary package hint: dbmail

Using current DBMail (2.2.11 as packaged or 2.2.13 from upstream) with the current MySQL server version (5.1) shipped with Ubuntu is unusable. The MySQL server does not use the index and falls back to a full table scan. This is a regression between MySQL Server 5.0 and 5.1 and is reported upstream as a serious bug:

http://bugs.mysql.com/bug.php?id=38745

The amount of time needed for a query not using the index grows with the table size and if the whole table doesn't fit into RAM (that's usually the case with dbmail) it will cause huge amounts of disk IO.
We have rendered a server completely unusable because of this in the initial testing phase while copying around 20G of Maildir-based accounts to DBMail using imapsync.

Just to name some numbers, before and after the fix, the same query took >100s compared to <1s respectively. Since this query happens _very_ often, it's a serious issue and people just starting out with DBMail and small databases won't notice it immediately.

The problem is non-existant with MySQL server version 5.0, which uses the index just fine. 5.1 needs the index to be used explicitely.

The problematic query as found in dbmail-message.c (line 747 and following as of 2.2.13):

static struct DbmailMessage * _fetch_full(struct DbmailMessage *self)
{
 char *query_template = "SELECT messageblk, is_header "
  "FROM %smessageblks "
  "WHERE physmessage_id = %llu "
  "ORDER BY messageblk_idnr";
 return _retrieve(self, query_template);
}

Changing it to explicitely use the correct index (USE INDEX()) fixes it:

static struct DbmailMessage * _fetch_full(struct DbmailMessage *self)
{
 char *query_template = "SELECT messageblk, is_header "
  "FROM %smessageblks USE INDEX(physmessage_id_index) "
  "WHERE physmessage_id = %llu "
  "ORDER BY messageblk_idnr";
 return _retrieve(self, query_template);
}

The same change can be applied to _fetch_head().

Thanks

[John Feuerstein (john-feuerstein)]

Thanks to Harald Reindl who pointed this exact problem out upstream:

http://bugs.mysql.com/bug.php?id=46508

The bug is fixed in mysql-server version 5.1.37, which is shipped with new Ubuntu Karmic.
Ubuntu Jaunty users will have to backport it or upgrade.

[Jorge Castro (jorge)]

Hi John can we go ahead and close this bug as fix released then?

[John Feuerstein (john-feuerstein)]

Yes, I can confirm it's fixed in MySQL 5.1.37 shipped with Ubuntu Karmic. The fix should be backported to Jaunty, though.