CREATE TABLE IF NOT EXISTS `Tournaments` (
`TournamentID` INT,
`PlayerID1` BIGINT,
`PlayerID2` BIGINT,
PRIMARY KEY (`TournamentID`),
INDEX `world_t_p1` (`TournamentID` ASC, `Player1` ASC),
INDEX `world_t_p2` (`TournamentID` ASC, `Player2` ASC));
I want to query all rows for one specific PlayerID using index. Are this 2 queries equal in terms of performance?
select * from Tournaments
where TournamentID=@tid and PlayerID1=@player_id OR TournamentID=@tid and PlayerID2=@player_id;
select * from Tournaments
where TournamentID=@tid and (PlayerID1=@player_id OR PlayerID2=@player_id);
You have the correct compound indexes with columns in a correct order for either query.
It seems likely the MySQL query planner will work the same way for either one, doing an index scan on each index in turn. But to know for sure you need to use EXPLAIN.
Pro tip: Don't write your code in a way that assumes the next programmer remembers the precedence rules for operators. That next programmer may be your future self. Parentheses help clarity and don't harm performance. In your example, write
WHERE (TournamentID=@tid AND PlayerID1=@player_id)
OR (TournamentID=@tid AND PlayerID2=@player_id)
even though it means the same thing as what you wrote. But your second formulation is clear too.