2

I need a faster alternative for 

SELECT * 
    FROM table 
    WHERE cat='catname' 
    ORDER BY RAND() LIMIT 6
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
Costel Cocerhan
  • 21
  • 4

2 Answers2

2

If the table is very big, shuffling inside your application could be very slow.

What about this solution:

Find out the size of the table (how many rows). than, programmatically find 6 random numbers between 0 and number_of_rows.

The second query for searching the rows: 

select * from table where id = id1 or id = id2...
hichris123
  • 10,145
  • 15
  • 56
  • 70
Kevin
  • 1,232
  • 10
  • 28
  • 1
    I think this is the right idea, though you would need to do this in a transaction or the size of the db might grow between count query and the select – frankc Oct 26 '11 at 18:24
0

The faster way is don't use RAND() in your query. Shuffle your results inside your application.

matino
  • 17,199
  • 8
  • 49
  • 58
  • For example, he has 500k rows, select them all and then shuffle in the app? :) – Mārtiņš Briedis Oct 26 '11 at 14:16
  • 1
    Exactly http://stackoverflow.com/questions/6592751/why-dont-use-mysql-order-by-rand – matino Oct 26 '11 at 14:18
  • The database can do this much faster than any in memory code can do it. So, this does not seem to be a faster alternative for the query. – Bas Slagter Oct 26 '11 at 14:19
  • @Briedis : any way he has to select all 500k from DB – sll Oct 26 '11 at 14:29
  • @Baszz - How can you prove it? The query without rand IS a faster alternative to the query and great relief to the database in most cases (mysql won't use cache for rand queries). It may or may be not to the application in general - this should be debugged in application but it is the simplest and easiest to implement alternative to check in the first place. – matino Oct 26 '11 at 14:32
  • Just made some quick tests with 1k record table, one with ORDER BY RAND other with PHP shuffle - avg. request speed for first one was 0.85s, for the 2nd one 0.6s. – matino Oct 26 '11 at 14:43