Support for Index Hints (Draft/For Consideration)

[crozone]

Preface

Occasionally the MySQL/MariaDB query planner will make a very sub-optimal (aka downright silly) decision when deciding which index to use for a query. This can have enormous performance implications, because within a single query execution MySQL can only use one index. Se also: this stackoverflow answer. Unfortunately this issue doesn't appear to be particularly rare, and depends on heuristics which run over table statistics, so can change unexpectedly at runtime.

MySQL/MariaDB support Index Hints (MySql/MariaDB), which can help direct the query planner to make the correct decision. These are primarily USE INDEX(), IGNORE INDEX(), FORCE INDEX(), and are specified after the FROM table_name` clause. Multiple hints can be specified.

The issue

EF and Pomelo do not currently expose an API for specifying index hints. Given how critical they can be to reliably performant queries, it may be convenient to expose them as an easy to use extension method.

In raw SQL, the index can be manually specified with SELECT * FROM table AS t USE INDEX(ix_table_some_column).

A workaround is to use .FromSqlRaw("SELECT * FROM table AS t USE INDEX(ix_table_some_column)"). EF will then turn this into a subquery, which is enough for MySQL to set the index for the query as a whole. However, hardcoding the SQL requires manually specifying the table and index names, which is much more fragile then a fluent API.

Proposed API

*Index() extensions on IQueryable<T>

.UseIndex(...) maps to USE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]). See also Use Index.

.IgnoreIndex(...) maps to IGNORE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]). See also Ignore Index.

.ForceIndex(...) maps to FORCE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]). See also Force Index.

All three extension methods have the exact same usage, with two overloads each:

• .*Index(string indexName, IndexLimitKind indexLimit = IndexLimitKind.Join)

  • Specify the index name directly.
  • Only a single index is specified. For multiple indexes, .*Index() can be invoked multiple times.

• .*Index(Func<TEntity, object>> indexExpression, IndexLimitKind indexLimit = IndexLimitKind.Join)

  • Use the same index expression style as used in the EntityTypeBuilder.HasIndex() method. The index name will be resolved automatically from the indexExpression, if it exists.
  • Single column index: .*Index(e => e.SomeColumn)
  • Multi column index: .*Index(e => new { e.ColumnA, e.ColumnB })
  • Only a single index is specified. For multiple indexes, .*Index() can be invoked multiple times.

IndexLimitKind:

enum IndexLimitKind {
    Join, // FOR JOIN
    OrderBy, // FOR ORDER BY
    GroupBy // FOR GROUP BY
}

Considerations

• All three hint clauses are capable of taking a list of indexes, however this appears to be functionally equivalent of using multiple clauses in sequence. So, multiple .UseIndex(), IgnoreIndex(), or ForceIndex() of the same index limit type ({JOIN|ORDER BY|GROUP BY}) may be concatenated into a single USE INDEX or IGNORE INDEX statement as a query size optimisation, but it does not appear to be required. This is also why the extension methods do not need to take a list of indexes.

• Currently there doesn't appear to be any Pomelo/MySQL specific extension methods on DbSet<T> or IQueryable<T>, so this would be breaking the mold by adding some.

Implementation

The extension method could probably set some "Tag" query annotations on the IQueryable to let Pomelo know to append the required Index Hints after the FROM table_name clause.

[mguinness]

There's an open issue in the upstream repo at https://github.com/dotnet/efcore/issues/6717, so take a look and upvote if appropriate. The general workaround is to use DbCommandInterceptor see How can I specify an index hint in Entity Framework? for an example.