[ISSUE] Index on encrypted column

[devendermishra]

Describe the bug In one of my table, there is an encrypted field e_phone. Table has 3 Million of rows. Without using an index, it is taking approx 5sec to search on e_phone field.

After creating a functional index as follows:

CREATE INDEX e_phone_index ON customer_detail((convert(substr(e_phone, 1, 33), binary)));

It is taking less time.

I want to know that what are the other suggested indexes to be create on encrypted columns for searching?

To Reproduce NA

Expected behavior NA

Acra configuration files NA

Environment (please complete the following information):

• Acra version: [0.95.0]

• Database server and its version: [MySQL 5.7, MySQL 8]

• Installed components:

  • [x] AcraServer

• Data-in-transit encryption between Acra and the client-side application:

  • [x] no transport encryption

• Installation way:

  • [x] via Docker

Additional context NA

[Lagovas]

I'm not sure, but you can try hash index type (only if it supports expressions as a column). Searchable encryption supports only ==/<> operations, so hash index is relevant and can be better. Would appreciate feedback if you would try it.

All other solutions are out-of-scope index capabilities. The next suggestion is partitioning which will reduce the size of the index and reduce amount of rows.