Mysql PHP Random Entries

Question

I read that it’s nice to run SQL queries with ORDER BY RAND () in large databases.

So here is my shot at breaking the code. The code should select 10 random identifiers from the database, then make a second choice to capture random strings.

$sql = "SELECT id FROM table WHERE image != '' ORDER BY id DESC LIMIT 50;"; $result = mysql_query($sql); while($row = mysql_fetch_array($result)) { foreach($row as $key => $value) { $array[] = $value; } } $rand_keys = array_rand($array, 10); foreach($rand_keys as $value) { $rand_arr[] = $array[$value]; } $rand_list = implode("," , $rand_arr); $sql = "SELECT image FROM table WHERE image != '' AND id IN ($rand_list)"; $result = mysql_query($sql); 

Any suggestions for speeding up or simplifying?

Answer 1

Five Four Things:

• Why do you choose 50 id if you only need 12? (you choose 12 identifiers from the last 50), which makes sense, although it is not particularly random in the general sense of the word - is it intentionally a subset of your lines from which you want to select random lines?).

• Have you profiled the SQL ORDER BY RAND() statement so that it is slow for you? How big is your data set?

• In the last request, you do not need WHERE image != '' , Since you have already selected the id for which image != '' .

• Why are you doing array_rand($array, 10) - you say you want 12 values?

• You can simplify the selection of random values, such as:

$rand_arr = array_rand(array_flip($array), 12);

Answer 2

I would agree with paragraphs 1 and 2 above - if you can make a selection of random data in your application at the same level as the data, the less loose you will need to write to do the same.

Answer 3

There is no particularly wonderful way to do this elegantly.

But you can crack it in several ways. If your dataset is the right size (too big for "order by rand ()" but not too big), have consistent identifier values ​​and usually don't delete a lot, you can always do something like this:

 SELECT MIN(id) as min, MAX(id) as max FROM table 

Generate some number N of random numbers between "min" and "max" (inclusive). Let me call it 50. If you never delete anything from the table, N will probably be 12. If you delete, do some napkin arithmetic and choose a good number. You are probably mistaken on the high side.

 SELECT * FROM table WHERE id IN (<your set of integers>) AND image_id = '' LIMIT 12; 

Make sure you have at least 12 results. If not, basically repeat and combine.

For large sets, this method should work much better than ORDER BY RAND (), especially if your identification sequence is not very sparse.

Answer 4

I would focus on point 5 of Dominic as a rather low effect for random data extraction. You can sort() also specify a list of identifiers (I assume MySQL detects this and skips sorting this list for you.)

There are other methods for large data sets and high queries that include materialized views (mainly table caching). Are you trying to solve an existing performance problem on a large, busy table?

Answer 5

Another option is to use a random serialized hash function and sort by it.

Get the maximum and minimum identifiers for the table and use the PHP rand () function to generate a random number between max and min.

Then use this number to seed your hash function. SQL association means a random integer generated by PHP

 SELECT image FROM table WHERE image != '' ORDER BY MOD(ABS({salt}-id), MOD({salt}, 10)), ABS({salt}-id)); 

You can optimize the bit by doing the calculation of MOD ({salt}, 10) in PHP and passing the value in the request.

Answer 6

If the line size is not too large, I would just select 50 lines and save a random list of 12 of them in the application. Yes, that means you throw away 80% of the selected rows. When you say that 80% of 50 is really a crime? It is a fact that SQL is not very good.