1

I had a question yesterday about ordering a mysql query by rand(). And I got a good answer here: https://stackoverflow.com/a/16597706/2333744

THe code for the answer is below.

create temporary table results as
(Select *, @rn := @rn + 1 as rn, rand() as therand
from table1 inner join
  table2
  on table1.in = table2.in cross join
    (select @rn := 0) const
where table1.T = A
);

select *
from results
where therand < 1000/@rn
order by therand
limit 500;

I understand everything except for

cross join (select @rn : = 0) const

I'm not sure what this is doing and if its important. When I remove it I get no performance change. Can anyone understand this part?

Lee Han Kyeol
  • 2,371
  • 2
  • 29
  • 44
Conrad Lewis
  • 55
  • 1
  • 12

2 Answers2

1

The User-Defined Variable @rn used in this case just for making a serial number column as explained in the answer of the previous question where you get this from.

The const is not used as a keyword here ... so don't be 'const-fused' by that. It is just a given name to (select @rn := 0) ... It could have been any other name like A, B, oops, aah, etc ... (see the second link below)

See example use in the folowing links to better understand the User-Defined Variables:

  • Create a Cumulative Sum Column in MySQL
  • MySql: Select Query- Make A Cumulative Sum Column
    • Community
    • 1
    • 1
    Tariq M Nasim
    • 1,278
    • 11
    • 24
    0

    frankly, all this does is to reset the @rn variable. Is is "packed" into a select to avoid running 2 queries. The const means that it is constant, hence only evaluated once.

    You could run into trouble when you remove it and add further queries into a single transaction.

    Best regards

    Zsolt

    Zsolt Szilagyi
    • 4,741
    • 4
    • 28
    • 44