Mysql: Any way to properly index 3 ENUM columns with the same options ? (A OR B OR C) I have 3 enum() columns with the same option values inside.
I first tried to use the &quot;set&quot; 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 (&amp;) 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)
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.