Query filter performance string vs bool

[Michael Roterman]

Hello,

I have a users table that need's a flag to indicate the status of the user. I see that many use something like u.active = true. I would however like to support more states such as 'active', 'suspended', 'deleted'. So I was thinking of doing a string attribute called status that would store one of these three. In MySQL I would use enum's for this. As I will filter on this attribute in almost every query I am curious if there are any performance aspects of different types?

I could also save different states as int's (1,2,3) and use a skiplist index.

Thanks!

[Jan]

Hello,

using a numeric value instead of a string should be slightly more efficient. This is because numeric comparisons will be cheaper. As a bonus, storing a numeric value may save storage space compared to saving strings.
The savings in time and space may not be great, but they may be there. Especially if a query is employing no other filters and needs to run the status filter on a lot of documents, there might be a small but measurable difference.

Best regards
Jan

[Michael Roterman]

Thanks Jan!

That is what I suspected. I am guessing that a bool and numeric value would be about the same then?

Regards,

Michael

[Jan]

Saving a bool or a numeric value should be about the same, provided that there are only two statuses.

Though I didn't test it with bools because you mentioned three status values, and saving them with bools would require having three boolean attributes, which will be less efficient than having a single numeric attribute.

[Michael Roterman]

Thanks! Yes I'll try and figure out what makes sense for my use case. Using int or bool with skiplist index should be the best option.