CODEWITHSUNDEEP
mysqlsqlselectunion
DimOK
DimOK

Reputation: 111

Mysql select condition

I have the following table:

match_id player_1 player_2
1        AAA      BBB
2        CCC      DDD
3        CCC      AAA

I want to know match_id of match between players AAA and BBB, but i don't know their exact order (AAA vs BBB or BBB vs AAA).

For now i use construction like WHERE (('player_1' = AAA AND 'player_2' = BBB) OR ('player_1' = BBB AND 'player_2' = AAA)), but it can't use keys, so i doubt it is the most efficient one.

It worked well on small table (1k+ entries), but now i am working on another project where 100k+ rows in this table is possible, so i am a bit worried.

Please, suggest me best way to get match_id knowing both players.

Upvotes: 0

Views: 402

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try UNION or UNION ALL to implement index on both column: 

SELECT match_id FROM tablename 
WHERE player1='AAA' AND player2='BBB' 
UNION 
SELECT match_id FROM tablename 
WHERE player1='BBB' AND player2='AAA'

OR you can also try this 

SELECT matchid FROM tablename 
WHERE player1 IN ('AAA', 'BBB') AND player2 IN ('AAA', 'BBB') AND 
      player1 != player2;

Upvotes: 0

Alexander Taver
Alexander Taver

Reputation: 474

Yes, using OR in condition definitely slows down your query. I propose to change approach a bit. Please, check whether your model allows to store players in 'sorted' way (by ID if you use IDs, or alphabetically if you use names): Every time you have two players to store, place to player_1 the one which goes alphabetically/numerically first, and in player_2 the another one.

Assuming what both AAA vs BBB and BBB vs AAA will always be stored as ('AAA', 'BBB'), you may shorten your query to:

WHERE ('player_1' = 'AAA' AND 'player_2' = 'BBB')

Upvotes: 0

adeel iqbal
adeel iqbal

Reputation: 494

try the query 

select match_id from table_name where "AAA" in (player_1,player_2) and   
"BBB" in (player_1,player_2);

and don't forget to create index of the player_1 and player_2 field. it would help to improve performance

Upvotes: 1

Related Questions