Efficiently Selecting Random Rows From a MySQL Table

It’s tempting to simply use MySQL’s RAND() function to select random records from a table. Consider the following query:

SELECT FLOOR(7 + (RAND() * 5));

However, when considering overhead, this is a rather expensive method to employ. This becomes glaringly apparent when dealing with large datasets.

One effective solution is to select the first and last ids, and let PHP generate the random integer (id).  Then, simply select the record using the random integer.

public function getRandomRecords()
{
	// Select the first id in the target table
	$statement = $this->db->prepare("SELECT some_id
	  FROM table_name
	  ORDER BY some_id ASC LIMIT 1");
	$statement->execute();
	$lowest_id = $statement->fetch(PDO::FETCH_ASSOC);

	// Select the last id in the target table
	$statement = $this->db->prepare("SELECT some_id
	  FROM table_name
	  ORDER BY some_id DESC LIMIT 1");
	$statement->execute();
	$highest_id = $statement->fetch(PDO::FETCH_ASSOC);

	$records_array = array();

	while(true)
	{
	  // Generate a random integer
	  $random_id = rand( $lowest_id['some_id'], $highest_id['some_id'] );

	  // Check to see if the record exists
	  $statement = $this->db->prepare("SELECT col_one, col_two, etc...
		FROM table_name
		WHERE some_id = {$random_id}";
		$statement->execute();
		$result = $statement->fetchAll(PDO::FETCH_ASSOC);

	  // If it exists, add it to the array
	  if($result)
	  {
	    $records_array[] = $record;
	  }

	  $i++;

	  // If the array contains 5 records, stop
	  if(count($records_array) == 5)
	  {
	    break;
	  }
	}

	return $records_array;
}

Video z-index?

Somewhat struggled to get a video to behave on a webpage. After throwing z-indexes around like they were going out of style, here’s what did the trick:

<param name="wmode" value="opaque">

Parsing Large XML Files Using PHP

I ran into a situation where I needed to parse a large (1 GB) XML file in order to extract the data into a MySQL table. As usual, I did my initial round of research. First, I decided to use the DOMDocument PHP class.

First Mistake

For my testing, I used a small subset of the data… weighing in at a measly 24 records.

Initially, all of my tests ran quite nicely. Then I decided to throw the complete (1 GB) XML file at it. Epic fail… I mean, it ran well for a while, but eventually ran out of memory. (And, yes… I did increase the memory_limit* to 1.5 GB and max_execution_time* to 5 hours.) I feared this may happen.

The problem with utilizing DOMDocument on large XML files is that it loads the data into an array. While parsing, that array is growing. Not good when you’re dealing with massive XML files.

With this fail under my belt, I went back to the drawing board. Knowledge is power… knowledge is power… knowledge is power.

My Next Move

XMLReader. From the PHP website: ”The XMLReader extension is an XML Pull parser. The reader acts as a cursor going forward on the document stream and stopping at each node on the way.” OK, sounds considerably more promising.

And Survey Says, Ding!

$file = "PATH_TO_FILE";

$reader = new XMLReader();

$reader->open($file);

while( $reader->read() )

{

// Execute processing here

}

$reader->close();

After that, it was gravy. Well, aside from the additional logic that had to go into it. That’s easily a topic all of it’s own, perfect for perhaps a “Part 2″ of this post. No promises though… unless, of course, incoming requests prompt for more information!

* How to modify PHP’s “memory_limit” and “max_execution_time” on a per script basis

// Tweak some PHP configurations
ini_set('memory_limit','1536M'); // 1.5 GB
ini_set('max_execution_time', 18000); // 5 hours