1

I have this query

$sql = mysql_query("SELECT DISTINCT a, b FROM table ORDER BY RAND() DESC limit 15");

Which worked great when my table has only a couple of rows, now my table has thousands this makes my page load really slow.

I have tried

$s = mysql_query("SELECT id FROM table");
$z = mysql_num_rows($s);
$n = rand(1,$z);
$sql = mysql_query("SELECT DISTINCT a, b FROM table limit $n, 15");

Which makes it a bit faster but it gives me a random row, then another 14 straight after it rather than all 15 being from random places, is there a better way to do this?

Reporter
  • 3,897
  • 5
  • 33
  • 47
user2458791
  • 15
  • 1
  • 6
  • What is desired output? – Fabio Jun 06 '13 at 08:31
  • Don't use mysql_ function because they are deprecated. – Kees Sonnema Jun 06 '13 at 08:33
  • 2
    off topic, but please note that the `mysql_xxx()` functions are deprecated and not recommended for use any more. You should be planning to switch your code to use the PDO library instead. – Spudley Jun 06 '13 at 08:33
  • @Fabio Looks like OP wants 15 records taken at random. – Denys Séguret Jun 06 '13 at 08:33
  • 1
    This [page from a simpole google search](http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/) directly answers your question – Anigel Jun 06 '13 at 08:34
  • It outputs 15 random rows, thats the desired output. I know mysql is deprecated we don't need reminded on every question. – user2458791 Jun 06 '13 at 08:34
  • You can find a really good and in-depth blog post about random select on big databases here: http://jan.kneschke.de/projects/mysql/order-by-rand/ – Arkh Jun 06 '13 at 08:36
  • 1
    Just repeat the `rand(1,$z)` 15 times, getting 15 random IDs, then just select them by ID. If you don't want dupicates you'll need a little extra code, but it will still be a lot faster than ODRER BY RAND() since it will be using the index on ID for fetching. – Lee Daniel Crocker Jun 06 '13 at 08:37
  • Use something like this http://pastebin.com/m6UGE37U and let me get feedback – Baba Jun 06 '13 at 08:46
  • Who voted to reopen this ? And why ? – Denys Séguret Jun 06 '13 at 16:27

0 Answers0