Poor optimization of JOIN and ORDER BY ... LIMIT

Description

A long standing (and informally known) issue:

Join optimizer makes its choices [almost] without regard for ORDER BY ... LIMIT clause. ORDER BY ... LIMIT optimizer is invoked when the join order is already fixed. If the picked join order doesn't allow to resolve ORDER BY ... LIMIT efficiently... then we end up with a very poor query plan.

Example:

This uses filesort and takes ~8 sec.
Now, let's force the right join order:

This uses index to resolve the ORDER BY ... LIMIT and the select takes 0.01 sec to execute.

Dataset:

Environment

None

Assignee

Sergei Petrunia

Reporter

Sergei Petrunia

Components

Fix versions

Affects versions

Priority

Major
Configure