Can index in an ENUM field bring any advantage?

Question

I was reading in a reply from Soen, that the fields of a table that have INDEX can optimize a query. Of course, as long as this query is known, such as a user survey by the email, for example.

I have here in the system a table Pessoa which has a field ENUM('M', 'F') called sex.

This table is very "chubby" and I would like to know if, if I put a INDEX in this field, there may be improvement in the consultation (Because we have reports that make a specific query for these values).

It is advantageous to use index for fields of the type enum or boolean (tinyint(1))?

Answer 1

I can’t say in Mysql specifically (nor do I think it matters, I hope it doesn’t :P). There is probably no advantage if it is an index with a single column and/or enumeration so simple, but I do not say with such certainty.

If it’s a record of engineers, there might be a profit in it when you go looking for women. Why keep looking in the entire database to get 1% of the lines? I can’t guarantee that the results will be that much better for that 1%, but they certainly won’t be for the 99%.

So it depends on the case and has to do tests with the actual database.

It would certainly be more interesting if the enumeration column/boolean were part of the composite index.

It would be more interesting if this column were calculated from others and was a helper precisely to filter a certain complex condition and facilitate certain searches.

Some databases even have special index for this by mapping the bits and creating a compact index. This index can be used in a specific condition, in general it is not used in a Boolean column. The index is created based on an expression where and the result is that it is indexed, so it can already quickly select the lines that satisfy this condition. This is done internally and the developer cannot previously create this type of index.

Ask with a case that shows that the index doesn’t always help how people think.