Reading a random row from a MySQL table with Perl DBI
2010-01-25 09:21:17
This is the code from one of the clients used in the post "Statistics on getting a random row from a table" to check for the quickest way to access a random row.
It does nothing but reading a thousand random rows, I hope it's enough for you to learn how to access MySQL databases using Perl and use it as a quickstart for your own scripts.
#!/usr/bin/perl
use DBI;
use DBD::mysql;
$platform = "mysql";
$database = "database";
$host = "server_ip";
$port = "3306";
$user = "user";
$pw = "12345";
$dsn = "dbi:$platform:$database:$host:$port";
$connect = DBI->connect($dsn, $user, $pw);
for ($i=0; $i<1000; $i++)
{
$query = "SELECT COUNT(1) AS total FROM bench_myisam";
$query_handle = $connect->prepare($query);
$query_handle->execute();
$query_handle->bind_columns(\$total);
if ($query_handle->fetch())
{
$rand = int(rand($total));
$query = "SELECT id FROM bench_myisam LIMIT 1 OFFSET $rand";
$query_handle = $connect->prepare($query);
$query_handle->execute();
$query_handle->bind_columns(\$id);
$query_handle->fetch();
}
}