CODEWITHSUNDEEP
mysqlsearchindexingwhere-clause
stack
stack

Reputation: 10218

How to search in multiple columns?

Here is my table:

// table
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1  | 1    | 1    |
| 2  | 1    | 2    |
| 3  | 1    | 3    |
| 4  | 2    | 1    |
| 5  | 2    | 2    |
| 6  | 3    | 1    |
| 7  | 3    | 2    |
| 8  | 3    | 3    |
| 9  | 3    | 4    |
| 10 | 3    | 5    |
+----+------+------+

Now I want to search in both col1 and col2. Something like this:

 select * from table where col1,col2 IN (1,2);

And I want this output:

+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1  | 1    | 1    |
| 2  | 1    | 2    |
| 3  | 1    | 3    |
| 4  | 2    | 1    |
| 5  | 2    | 2    |
| 6  | 3    | 1    |
| 7  | 3    | 2    |
+----+------+------+

Well, My problem is on this part: ... where col1,col2 IN (1,2). How can I solve it?

Note: I can do that like this: ... where col1 IN (1,2) or ,col2 IN (1,2). But this this way, I have to create two separate index on each column. While I want a query which need to a group-index like this: KEY NameIndex (col1, col2)

Upvotes: 2

Views: 50

Answers (1)

Rick James
Rick James

Reputation: 142218

You want this, correct?

WHERE col1 IN (1,2)
   OR col2 IN (1,2)

If so, turn the OR into a UNION. (This is a common optimization trick.)

( SELECT ... WHERE col1 IN (1,2) )
UNION DISTINCT  -- since there are likely to be dups
( SELECT ... WHERE col2 IN (1,2) );

And provide the optimal index for each SELECT:

INDEX(col1),
INDEX(col2)

A composite index of those two columns will not suffice.

(Appologies -- this is probably a summary of best of the many disjointed comments.)

Upvotes: 2

Related Questions