Mysql: Any way to properly index 3 ENUM columns with the same options ? (A OR B OR C)

Question

I have 3 enum() columns with the same option values inside.
I first tried to use the "set" datatype which originally was meant to do that (hold multiple values from a set) but it seems that datatype isn't managed for 15+ years and isn't even supporting an index.
Is there a nice way to index those 3 columns so I can use them in searched without destroying query performance ?

SELECT * FROM TABLE WHERE a='x' OR b='x' OR c='x'

I thought about creating a virtual field which uses a boolean logic (&) on the 3 enum field-numbers and combines them into a large number but that's quite a hack and not nice to maintain.

Has someone solved this sort of task elegantly ? (I do not want to use a support table and JOIN it, I want to stay with a single table)

Answer 1

UNION

You have listed most of the approaches. Here's one more:

SELECT ... WHERE a='x'
UNION ALL
SELECT ... WHERE b='x'
UNION ALL
SELECT ... WHERE c='x'

And have separate (not composite) indexes:

INDEX(a), INDEX(b), INDEX(c)

Each SELECT will efficiently use one of the indexes. Then the UNION will combine the results.

Use UNION DISTINCT if you expect dups and need to remove them.

that datatype isn't managed for 15+ years

Yeah, SET has been stable for a long time (more than 20 years). I tend to use some sized INT together with boolean operators instead of SET. But a table scan is needed in either case.

How big is your table? 1000 rows is hardly worth worrying about performance on.

FULLTEXT

Even faster (in some situations):

FULLTEXT(a, b, c)

WHERE MATCH(a, b, c) AGAINST('+x' IN BOOLEAN MODE)

(No OR, no UNION, etc.) If Fulltext is applicable, this will be very fast.

Answer 2

Because you need to use OR logic in your WHERE predicate, there's not a way to create one index that covers every case unfortunately.

Fortunately you're basically on the money with a virtual field or rather you should create a generated column, and then you can create an index on that single column so your queries are performant.

Answer 3

There's a reason why set isn't managed anymore/not supporting an index: It's essentially just a bitwise representation for a set to minimize memory usage.

However, an index on a set column supporting performant set operations would require multiple indexes in the background for each every, as if one would just make a boolean column for each possible set entry and then made a separate index per column (to make checking if a value is in the set fast), and also possibly other indexes of column combinations.

All in all this would no longer be space efficient and the expected performance for filtering by the set/writing to the column would be easily comprehensible.

You're better off making separate boolean columns or another table with 1:n relationship and define the indexes the way you need them because a catch all set index would be too costly to maintain in most cases