I've created a "simple" function that receives a MySQLi resource and, using a custom where
query, will fetch random IDs from a provided table.
Some caveats on this:
- No object-style! This needs to be understood by people who only know the old
mysql_*
procedural API; - No
order by rand()
! It is slow, but very tempting; - It has to allow me to fetch a fixed number of IDs, or all available IDs;
- It must always return something, even if it is a simple array;
- It is required to allow for a table name to be passed;
- A custom
where
is required, since I need to provide complex queries; - It doesn't really matter if it is a truly random function: pseudo-random is perfectly fine;
- No prepared statements: this function will be used alongside some prepared statements. Those will give errors like "Commands out of sync; you can't run this command now";
- The code needs to run on PHP 5.3.29.
And here's the code:
<?PHP
function get_random_ids_from_table(&$link, $table, $where = null, $length = -1)
{
$ids_r = mysqli_query($link, 'select id from `' . $table . '`' . ($where ? ' where ' . $where : ''));
if(!$ids_r)
{
return array();
}
$count = mysqli_num_rows($ids_r);
if(!$count)
{
return array();
}
$ids = array();
if($length < 1 || $length > $count)
{
while($id = mysqli_fetch_assoc($ids_r))
{
$ids[] = $id['id'];
}
mysqli_free_result($ids_r);
shuffle($ids);
}
else
{
while(count($ids) < $length)
{
$offset = mt_rand(0, $count - 1);
if(!isset($ids[$offset]))
{
mysqli_data_seek($ids_r, $offset);
$id = mysqli_fetch_assoc($ids_r);
$ids[$offset] = $id['id'];
}
}
$ids = array_values($ids);
}
mysqli_free_result($ids_r);
return $ids;
}
This function works perfectly fine as-is, but it has a problem: it's still slow! It takes 500-1400ms when executed 4 times! I've already reduced its execution time from 3s. (Old version, for reference: http://pastebin.com/ASfBfWmg). And this is to select IDs from 500 rows. I'm expecting that the code runs on 2000+ rows.
How can I improve its horrendous speed? And which other changes you think that are necessary to make it more readable?
mysqli_data_seek()
, which may require that all the rows are fetched and kept somewhere. \$\endgroup\$SELECT ... ORDER BY RAND() ... LIMIT ? ...
query would greatly simplify your code. Try it out to see what query times you can expect from the knowingly sub-optimal randomized query. You might find it is reasonable compared to your current approach. Your number of rows should not be a problem at all for a randomized query to give "reasonable" performance. \$\endgroup\$