SQL Timeout error when trying to retrieve random row.

[f2fserv]

Hi. Trying to setup a MySQL Database I've created a db with one table (for now, adding more in the future). Every table has 5 columns, named "nom_base","nom","edat","centre" and "imatge". I'm not using indexes to make faster queries.

What I want to do is: Given an array with the name of every table, select one table. Then, inside that table, I want to select a random row, fetch it as an array and assign it's data to some variables. I tried this code:

<?php //DATABASE_SCRIPT.PHP
$db_user = "username"; // Username
$db_pass = "pass"; // Password
$db_database = "DB"; // Database Name
$db_host = "localhost"; // Server Hostname
$database = mysql_connect ($db_host, $db_user, $db_pass); // Connects to the database.
$db_connect = mysql_select_db($database);// Selects the database.

function select_random_row($table){
   $result = mysql_query("SELECT * FROM ".$table." ORDER BY RAND() LIMIT 0,1");
   
   // ALTERNATE METHOD: (can't get it to work)
   //$string =  "SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM ".$table;
   //$offset_result = mysql_query($string);
   //$offset_row = mysql_fetch_object( $offset_result ); 
   //$offset = $offset_row->offset;
   //$result = mysql_query( "SELECT * FROM ".$table." LIMIT ".$offset.", 1");
   
   return $result;
}
?>

<?php //EMBEDDED IN INDEX.PHP
$CENTRES = array(
    "COSTA" //Only one table at the moment
);

$row1 = "";
$row2 = "";
while ($row1 == $row2){
  $row1 = select_random_row($CENTRES[array_rand($CENTRES, 1)]);
  $row2 = select_random_row($CENTRES[array_rand($CENTRES, 1)]);
  
$row1DATA = mysql_fetch_array($row1);
$row2DATA = mysql_fetch_array($row2);

$Nom_base1 = $row1DATA['nom_base'];
$Nom1 = $row1DATA['nom'];
$Edat1 = $row1DATA['edat'];
$Institut1 = $row1DATA['centre'];

$Nom_base2 = $row2DATA['nom_base'];
$Nom2 = $row2DATA['nom'];
$Edat2 = $row2DATA['edat'];
$Institut2 = $row2DATA['centre'];
}
?>

I get this:

Fatal error: Maximum execution time of 30 seconds exceeded

regarding this line:

$result = mysql_query("SELECT * FROM ".$table." ORDER BY RAND() LIMIT 0,1");

inside the select_random_row($table) function.

I'm guessing that the method I use to retrieve a random row doesn't work, but I have no clue.

 

[descalzo]

How many rows in COSTA?

And since you want two rows, why not have the function return two rows in the first place?

 

[kloadx10]

Make it LIMIT 1 what LIMIT function does is that it selects the first row count number till the last row count number but if you only want to select a fixed amount of row then use only one digit like:

SELECT * FROM table_name ORDER BY RAND() LIMIT 1

This will select only the first row fetched

SELECT * FROM table_name ORDER BY RAND() LIMIT 5, 10

This will select the 5th row till the 10th row fetched

 

[misson]

No matter what, ORDER BY RAND() will require a table sort since RAND() isn't in any index (indeed, can't be).

One alternative if the table has an integer ID column (which it should, as a surrogate) is to get the largest ID, generate a random number <= the largest ID and retrieve the row with that ID. If the ID column values aren't contiguous, you can either try again (if there aren't too many gaps, and the gaps are small, there won't be many re-rolls) or fetch the row with the next higher (or lower) ID. The latter won't produce a uniform distribution, as a row next to a gap of k IDs will have a probability of k/n of being picked, where n is the number of rows in the table.

SELECT $fields
    FROM $table
    WHERE id >= (SELECT CAST(RAND()*MAX(id)+1 AS UNSIGNED) FROM $table)
    LIMIT 1

Mixed SQL+PHP:

/**
 * WARNING: Performs no sanitization on $table or $fields.
 *
 * @returns a PDOStatement with exactly one random row from $table.
 */

function select_random_row($db, $table, $fields='*') {
    static $countQrys = array();

    $dbHash = spl_object_hash($db);
    if (! isset($countQrys[$dbHash][$table])) {
        $qry = $countQrys[$dbHash][$table] = $db->prepare("SELECT COUNT(*) FROM $table");
        $qry->setFetchMode(PDO::FETCH_COLUMN, 0);
    }
    $countQrys[$dbHash][$table]->execute();
    $count = (int) $countQrys[$dbHash][$table]->fetch();

    # LIMIT is 0-based
    $row = mt_rand(0, $count-1);

    return $db->query("SELECT $fields FROM $table LIMIT $row,1");
}

/**
 * WARNING: Performs no sanitization on $table or $fields.
 *
 * $table must identify a table with an "id" column. To guarantee that this function returns exactly $count rows, the id column must have no gaps.
 *
 * @returns a PDOStatement with at most $count rows.
 */
function select_random_rows($db, $table, $count, $fields='*') {
    static $maxIdQrys = array();

    $dbHash = spl_object_hash($db);
    if (! isset($maxIdQrys[$dbHash][$table])) {
        $qry = $maxIdQrys[$dbHash][$table] = $db->prepare("SELECT MAX(id) FROM $table");
        $qry->setFetchMode(PDO::FETCH_COLUMN, 0);
    }
    $maxIdQrys[$dbHash][$table]->execute();
    $maxId = (int) $maxIdQrys[$dbHash][$table]->fetch();

    # if $count is large, should store generated IDs as keys rather than values.
    $ids[] = mt_rand(1, $maxId);
    for ($i=1; $i < $count; ++$i) {
        do {
            $id = mt_rand(1, $maxId);
        } while (in_array($id, $ids));
        $ids[] = $id;
    }

    $ids = implode(', ', $ids);
    # prepared statements are of no help with variable length lists and identifiers.
    return $db->query("SELECT $fields FROM $table WHERE id IN ($ids)");
}

Since the functions keeps internal state, it would be a more natural to instead implement it as a method of some class. Exactly what class depends on the design of the rest of the system, and what should have the responsibility.

Don't use SELECT * unless you're writing a DB administration program; select only the columns you need.

The mysql extension is outdated and on its way to deprecation. Instead, use PDO, which has many useful improvements, such as prepared statements and support for the Traversable interface, so you can loop over results with foreach.

SO has this same question posted multiple times:

• MySQL: Alternatives to ORDER BY RAND()
• Random Row in MySQL
• quick selection of a random row from a large table in mysql

If you're tempted to post questions on StackOverflow, first read its FAQs (especially the MetaSO FAQs), as SO likely doesn't work the way you may first thing it does. SO isn't a forum but a Q&A site, a sort of super-FAQ for coding issues. Chances are, your questions have already been answered somewhere on the site.

 

[f2fserv]

Sorry for not answering sooner. misson's answer helped me a bunch, I didn't know how to make use of some of those functions, and it solved my problem in a blink. I did already know SO, but I thought this could also be a good place to ask. I will keep it in mind, though, and check SO next time before posting (and post there if I don't see my question answered).

Thanks!

 

[misson]

If you post on SO, just make sure that the question is appropriate and you have a good write-up. Jon Skeet's article (linked in my sig) on writing the perfect question was written for SO in particular. SO has become more limited in scope as other StackExchange sites have opened up. There are also plenty of other sources; asking a question to get information online is often unnecessary.