MySQL: Select two values of two columns and compared their values exchanging them each other

Question

I need to find a way to select one random row where the subscriber is not also leader (in the same or any other row).

Something like this: (just to have an idea)

SELECT `leader`,`subscriber` FROM `relations` WHERE `subscriber`.`leader` != `leader`.`subscriber` ORDER BY RAND() LIMIT 1

This is the table relations:

+---+--------+-----------+
| id| leader | subscriber|
+---+--------+-----------+
| 1 |       2|          5|
| 2 |       5|          2|
| 3 |       4|          8|
| 4 |       8|          4|
| 5 |       9|          6|
+---+--------+-----------+

So in this example is 9 and 6 (last row).

Is it possible to achieve this with just one query?

UPDATE

I noticed a long execute time for this query (about 40 secs)!

This is the output for my table relations:

| relations | CREATE TABLE `relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leader` int(11) NOT NULL,
  `subscriber` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35207 DEFAULT CHARSET=latin1 |

Answer 1

I guess, from the description, that you first need all rows with rows (x,y) where no other row (y,x) exists. Then select a random row from them:

SELECT r.leader, r.subscriber 
FROM relations AS r 
WHERE NOT EXISTS
      ( SELECT *
        FROM relations AS r2
        WHERE r.subscriber = r2.leader
          AND r.leader = r2.subscriber     -- or perhaps without this
      ) 
ORDER BY RAND()
LIMIT 1 ;  

It would be useful to have two indexes, on (leader, subscriber) and on (subscriber, leader). If the table is very big and performance is still not good, you may have to change how the random row is selected. The ORDER BY RAND LIMIT 1 solution works ok in small tables but it's quite slow in big tables as it has to sort all the matching rows before it chooses one.