A user table, as follows:
Id name manager1 manager2 manager3
1 * * A B C
Li si E A 2 D

Demand for managers with A data in 123,

Is there a good way to query or to combine three fields through the algorithm into a field, and then the query?
What kind of high efficiency? What's the algorithm can be implemented?

CodePudding user response:

Col1='A' or col2='A' or col3='A'

CodePudding user response:

1/f, positive solution
Select * from table name where manager1='A' or manager2='A'='A' or manager3

CodePudding user response:

Manager1='A' or manager2='A'='A' or manager3 inefficient, there is no other good way,

CodePudding user response:

reference gaopengsui reply: 3/f

manager1='A' or manager2='A'='A' or manager3 inefficient, there is no other good way,

If these three columns have index, automatically optimization, if one arbitrary column no index, you will be a full table scan
Have index is equivalent to

 select * from table name where manager1='A' union 
Select * from table name where manager2='A' union 
Select * from table name where manager3 

='A'

CodePudding user response:

The or for union all writing better efficiency

CodePudding user response:

reference 5 floor baidu_36457652 reply:

or replaced by union all writing efficiency better

The or inequitable union all, should be replaced by the union
Key or index, and any fields no index, need a full table scan
If two or three fields no index, or need a full scan, the union need to scan 2-3 times