0

I have a very simple MYSQL table with 2 columns and I run this query:

SELECT * FROM table WHERE (col1 = '123' AND col2 = '456')
                       OR (col1 = '456' AND col2 = '123')

Col1 and col2 are a composite primary key: PRIMARY KEY('col1','col2'). Each of both is also a foreign key for primary key in another table

When I ran the EXPLAIN command for the above query i got the following:

id  select_type  table  type    possible_keys   key key_len ref rows    Extra
1   SIMPLE       table  index   PRIMARY,col2    col2    8   NULL    1   Using where; Using index

The type in the above result is index which is very similar to All and so very likely to be slow on a large database. Is there a way to improve the above select command

1

What about using a union?

SELECT * FROM table WHERE (col1 = '123' AND col2 = '456') UNION ALL SELECT * FROM table WHERE (col1 = '456' AND col2 = '123')

  • I'd use UNION instead of UNION ALL – Manny Calavera Nov 6 '14 at 2:33
  • Yep good point @MannyCalavera ! – Ben Oastler Nov 6 '14 at 2:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.