0

Given a table of N records, what would be considered the fastest way of getting a single random PK from that table(for later use in php)?

The two methods I'm wondering about are :
mysql : perform the random query directly and get a single ID as the query result.
php : get a list of IDs from mysql and use array_rand() to fetch one at random

Also : Does scale matter for such a query? Would a table N=200 records be faster using mysql but a table of N=200,000 be faster with php?(or vice verca)

My intuition tells me that MySQL should be faster and use much less memory, as you must create an array of N keys using the php method and store them at least temporarily.

Patrick
  • 3,289
  • 2
  • 18
  • 31
  • 1
    Actually the fastest way, when talking about sequential IDs, is to just get first, last and then pick a random number from between the two. Even if you will have to retry couple times it still will be vastly faster than any of the above and scales nicely. – Tymoteusz Paul Aug 29 '14 at 12:31
  • If you're doing it in PHP, you still need to store the array to memory, get first and last keys and select a random number. so CPU wise I agree it's really fast, But as a side note isn't memory also an issue?( I know i didn't ask that, But i'm wondering..) – Patrick Aug 29 '14 at 12:34
  • @Puciek It implies that your array is made of a list of ID without any ID missing between the "first" and the "last" one though. – Clément Malet Aug 29 '14 at 12:34
  • @ClémentMalet you are correct, but then there is partitioning which is a must for large scale tables (simply partition them according to your search conditions). – Tymoteusz Paul Aug 29 '14 at 12:36
  • 2
    http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql – Steve Aug 29 '14 at 12:38

0 Answers0