0

I have a table A with a foreign key to table B and I need to select 10 random fields from A such that fields in consecutive pairs have different b_id, i.e. a valid result would be: (x1, x2) (x3, x4) ... (xn, xn+1) where xi are the selected fields and if we name fi the foreign key of xi to B, then fi should different of fk+1. I have tried a really awful query which I don't like at all and I believe slow.

This is my current query:

select a1.b_id, a1.x, a2.b_id, a2.x
from A a1, A a2
where a1.b_id <> a2.b_id
order by rand()
limit 5
educampver
  • 2,967
  • 2
  • 23
  • 34

1 Answers1

0

I think your query is good with the exception of order by rand(), which will assign a random value to all of your rows, and then order them all before fetching the first 5. This is very expensive when you only really need 5 rows. This question has more efficient methods for getting random rows from MySQL: How can i optimize MySQL's ORDER BY RAND() function?

After you generate this two column query, you can transform it into a single column query by:

  1. save the result in a temporary table
  2. union the first column to the second column, marking where it came from
  3. select from the union in order

I have include an example below. Replace the insert line with your query.

create temporary table x (
  x1 int, x2 int);

insert x values (1, 2), (3, 4), (5, 6) ,(7, 8), (9, 10);

select v 
from (
  SELECT
    x1 AS v,
    1 AS pairPosition,
    @curRow1 := @curRow1 + 1 AS row_number
  FROM x
  JOIN    (SELECT @curRow1 := 0) r

  UNION

  SELECT
    x2 AS v,
    2 AS pairPosition,
    @curRow2 := @curRow2 + 1 AS row_number
  FROM x
  JOIN    (SELECT @curRow2 := 0) r
) xx
order by xx.row_number asc, pairposition asc
Community
  • 1
  • 1
John Tseng
  • 6,262
  • 2
  • 27
  • 35
  • Thanks for the reply. Now I have a new problem which could be asked as a new question: I need to select the pairs as a single column, i.e. `x1, x2, x3, x4, ... xn, xn+1`, not as two as my original question. Any idea? – educampver Jun 29 '13 at 21:38
  • @e.campver I've updated my answer with a crude method to change the 2 column solution into a single column. Unfortunately I had to use a temporary table since the solutions (unpivot, cte, or temporary variable) I had in mind are not in MySQL. Even the row number is ugly. – John Tseng Jun 29 '13 at 22:24