Support hash indexes

[mpskovvang]

It is not possible to use the index in a columnstore migration:

COLUMNAR indexes and SKIPLIST indexes cannot be used on the same table

Would it be possible to detect and default to hash indexes automatically?

Alternatively add a new hashIndex or usingHash similar to withoutPrimaryKey.

$table->sortKey();

$table->index(); // defaults to hash index
$table->index()->usingHash(); // or $table->index()->using('hash');
$table->hashIndex();

[carlsverre]

@srdante what do you think about this?

[miguilimzero]

From what I understand from the documentation would be to create a method to define hash indexes?

INDEX(...) USING HASH

I confess that I didn't understand very well looking at the documentation of how this works. The topic "Using HASH behavior for multiple columns" makes it seem like this is already the default.

https://docs.singlestore.com/managed-service/en/reference/sql-reference/data-definition-language-ddl/create-table.html#using-hash-behavior

But as far as I understand, this could also be applied to unique keys. I believe any of the two ways that @mpskovvang suggested would work without problems:

$table->hashIndex();
$table->hashUnique();

OR

$table->index()->usingHash();
$table->unique()->usingHash();

[carlsverre]

Sounds good. I like the idea of adding explicit keys the most, but also ok with the modifier. Would that be easy for you to add at some point?

[mpskovvang]

@srdante Good point regarding UNIQUE keys as well.

However, PRIMARY key defaults to hash behaviour. It automatically translates to UNIQUE KEY PRIMARY (id) USING HASH. I'm unsure if this is also the case with UNIQUE keys.

[miguilimzero]

@carlsverre I went deep into the index migration code, and this option is already possible. The index method structure is:

public function index($columns, $name = null, $algorithm = null)

The $algorithm parameter defines the using x statement. So the following code generates the index using hash:

$table->index('name', 'my_name_index', 'hash');

[miguilimzero]

I think it would be good to add a note about that in the README since its not mentioned in the Laravel documentation.

[mpskovvang]

@carlsverre I went deep into the index migration code, and this option is already possible. The index method structure is:

public function index($columns, $name = null, $algorithm = null)

The $algorithm parameter defines the using x statement. So the following code generates the index using hash:

$table->index('name', 'my_name_index', 'hash');

Cool, did not know about the $algorithm parameter. Thanks!