0

What is the most efficent method to extract 1000 random rows from a table with 100.000.000 of records without a numeric primary key?

Using ORDER BY RAND() is excluded because highly inefficent.

Looking around the best method i found is the following:

SELECT key FROM (
    SELECT @cnt := COUNT( * ) +1, @lim := 1000 FROM table
)vars STRAIGHT_JOIN (
    SELECT r . * , @lim := @lim -1
    FROM table r
    WHERE (
        @cnt := @cnt -1
    )
    AND RAND( ) < @lim / @cnt
)i

is this the best possible method using MySQL 5 or is it possible to use more efficent code?

user1014351
  • 437
  • 1
  • 5
  • 16
  • 1
    Whats wrong with `Select ket from table limit 1000` – Sashi Kant Oct 17 '13 at 09:17
  • possible duplicate of [Selecting random rows with MySQL](http://stackoverflow.com/questions/6541644/selecting-random-rows-with-mysql) – Alma Do Oct 17 '13 at 09:18
  • It's not a duplicate, i'm interested to the most generic case (i.e. alphanumeric primary key, primary key with more than one column etc.) I found that many solutions already proposed work well only for particular cases i.e: autoincrement index with no holes or with even distribution of value. Note: i want exactly 1000 rows, not solutions that give 1000 rows in 99,9% of cases. – user1014351 Oct 17 '13 at 09:31
  • @SashiKant it's not random. If i make the same query more than one time i will obtain very similar results. Also i don't want Select key from table limit RAND()*(number_of_rows-1000),1000 because only the first row is really random. – user1014351 Oct 17 '13 at 09:36
  • @Alma Do Mundo please delete the "possible duplicate" flag – user1014351 Oct 17 '13 at 09:41

1 Answers1

-1

In case you just want to get the first 1000 entries (but you can also declare an offset), you should look at Limit in MySql

http://dev.mysql.com/doc/refman/5.0/en/select.html

VanGoghsCoffee
  • 58
  • 2
  • 9