1

How to create a Mysql hash index using Entity Framework (core) ?

Having the index in the DbContext :

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<ContactEndpoint>().HasIndex(ce => ce.Endpoint);
}

Will create a migration with :

migrationBuilder.CreateIndex(
                name: "IX_ContactEndpoints_Endpoint",
                table: "ContactEndpoints",
                column: "Endpoint");

But how to specify the index type ? Hash / B-Tree ?

Thanks.

Bisounours
  • 11
  • 1

1 Answers1

-1

MySQL does not have "hash" indexes. (OK, there is a case, but you probably have not hit it.)

The keyword HASH is silently turned into BTree.

BTree indexing is about as fast as Hash (if it existed) for "point queries". Hash is useless for "range queries". So the designers of MySQL decided not to implement Hash.

So forget about "hash indexes". Welcome out of the textbook into the real world.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1
    You're right except one thing: it is not always silently turned into BTree. Look here https://stackoverflow.com/questions/50525020/getting-an-entity-framework-error-running-migrations-on-a-mysql-database-incor or here https://stackoverflow.com/questions/50102420/ef-incorrect-usage-of-spatial-fulltext-hash-index-and-explicit-index-order for example. People write their own solutions for this. Welcome out of the real world into the buggy world – Jahjajaka Mar 03 '21 at 14:47
  • 1
    @Jahjajaka - Yeah. An awfully lot of the questions on this site have to do with 3rd party software building not-quite-right SQL. Those links might help others more than my Answer. – Rick James Mar 03 '21 at 17:45