Indexing, how to implement correctly in Firebird delphi?

Question

Delphi

Hey!
Given:
1. firebird database, with two tables:
- table1 (ID, NAME, IDCOUNT)
- table2 (ID, HASHCOUNT) hashcount - string, encrypted number.
2. a delphi program that can load this data and display it decrypted.
Task:
When outputting to a table, you need to sort the data by the value of the count, that is, by the number.
Came to a conclusion that it is necessary to make indexing of the data and to store indexes.
But the creation of indexes turned out to be very expensive. Maybe someone has ideas about this.
I create indexes like this:
1. I get all the values ​​from table2
2. I decrypt it and put it in an array, and an array of indexes is also created.
3. I sort the indexes according to the Hoare sorting method.
4. I unload the resulting indexes in the database.
When selecting data from the database, I also select the indexes and sort by them.
It seems like everything is needed. but creation of indexes very slow turned out.
The data is updated frequently and therefore it is often necessary to index it, which slows down the work very much.
If anyone has any ideas on a different solution, I would be very grateful.

Answer 1

it's hard to understand why, but Firebird will not be able to sort accordingly when issuing the result, because the index must be built on the true value of the field and not on the hash.
There are 3 solutions:
1. decrypt your value into a calculated field and sort it in the program (on the client): DataTable.Filter:='FIELDNAME',DataTable.Filered:=true;
2. we don’t mess with encryption (because your encoder will be easily opened by some thoughtful pro) and store everything in the clear
3. it will work almost the same as yours slowly, current on the server (write UDF for decryption on the server)

Answer 2

There is also a 4th option: write your own external function for FB, decrypting the value and working with it directly in the database.
There is also a fifth option. Decrypt into a temporary table and work with it. After completion of work the table to kill.