Why does manual hash tag implementation improve the performance of my requests?

Question

In my model, I set Entity (let's say Person) to have an attribute in the form of a string (called "name"), and I put an index on it. If I ask a lot of queries on my model, the queries go beyond performance. My request is simple

[ NSPredicate predicateWithFormat: @"%K == %@", @"name", lPersonName ]; 

therefore, I would suggest that the index do its job.

Then, if I compute some simple hash tag and store it along with my entity in an indexed integer attribute (called a hash) and make a narrower query, performance loss is reduced. Like this:

 [ NSPredicate predicateWithFormat: @"%K == %d AND (%K == %@)", @"hash", [ self calculateHashForName: lPersonName ], @"name", lPersonName ]; 

Why is an index on integers much faster than an index on a string? Am I missing something? Is this a problem with master data?

I can save the solution with a hash tag, of course, but if I miss something, I would like to know about it sooner rather than later.

Answer 1

There are (at least) two possibilities:

• Name strings are larger than integers, so the required storage space is larger for the string index. Database performance directly depends on the size of the storage, as the cost of the search is measured in disk requests (ignoring caching at the moment), and the more data, the more requests are executed

• SQLite stores tables in B-trees and can use INTEGER PRIMARY KEY to search for a record with maximum efficiency without an additional index - this is not clear (to me) from your code if the hash is used as the INTEGER main key

Answer 2

On low-level computers, integers are produced, processors have an internal data type for an integer, but there is no internal data type for strings (in any case, ARM and x86).

 4000000000 == -123456789 

It can be processed by a computer in 1 instruction, and ...

 "Abcdefg" == "Abcdefzzzz" 

You need to scroll through the characters following a few instructions.

This is pretty generalized, but it comes to the core of the problem. In short, computers process integers faster, and although strings can be expressed as integers (binary bytes), they have a variable length, which makes them more difficult to process.