[Bug 482121] [NEW] MySQL 5.1 optimizer not using index

[John Feuerstein]

Public bug reported:

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

** Affects: dbmail (Ubuntu)
     Importance: Undecided
         Status: New

-- 
MySQL 5.1 optimizer not using index
https://bugs.launchpad.net/bugs/482121
You received this bug notification because you are a member of Ubuntu
Bugs, which is subscribed to Ubuntu.

-- 
ubuntu-bugs mailing list
ubuntu-bugs at lists.ubuntu.com
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs