0

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 |
  • 3
    You first say I need all subscribers and then use limit 1, do you need all or one? – Lamak Dec 7 '15 at 15:56
  • @Lamak I need to check all rows and then limit 1 – NineCattoRules Dec 7 '15 at 15:57
  • 1
    checking all rows is very different than selecting all rows – Lamak Dec 7 '15 at 15:58
  • @Lamak I need to select values leader and subscriber of only 1 row – NineCattoRules Dec 7 '15 at 16:00
  • 3
    So you want only one (random) subscriber that meets your criteria, not all of them, correct? – Andriy M Dec 7 '15 at 16:00
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.

  • I cannot use it, this SQL takes too long time to execute – NineCattoRules Mar 15 '16 at 16:50
  • 1
    Can you edit the question and add the output of SHOW CREATE TABLE relations;? How big is the table? – ypercubeᵀᴹ Mar 15 '16 at 16:53
  • Ok, I just updated the question – NineCattoRules Mar 15 '16 at 17:07
  • 1
    I have updated the answer with useful indexes to add. Without any indexes on subscriber and leader, it's no wonder that the query is slow. Also, you don't need that UNIQUE id index. The PRIMARY KEY is identical. – ypercubeᵀᴹ Mar 15 '16 at 17:25

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.