Trace

I have a very huge SQL table (5m entries and keep increasing) and want to select one row of entry at random. Typical method of using one SQL statement is apparently so slow. The typical method is by using the below statement by the way.

SELECT <column_name/*> FROM <table_name> ORDER BY RAND() LIMIT 1

while the above statement works fine on smaller table – generally 10000 records and below (depending on your processor speed), when its confronted with huge table, the statement just could not make it. Just to give you some imagination, I start using one of those statement and while waiting for the result, I can go to vending machine, grab a coke and come back, finish half a bottle of coke before stopping the process out of frustration.

After much tweaking and such, I realized that some statement are more size-conscious than the other. The trick is that using several statements which are less size-conscious to gather much needed parameter to assist statement that are more size conscious by increasing its … specifity (TODO: rephrase this paragraph)

I implement these principles using Java and the result can be seen below

public synchronized PhotoBean getRandomPhoto() throws DAOException {
        Connection con = null;
        try {
            con = getConnection();

            PreparedStatement pstmt1 = con
                    .prepareStatement("SELECT max(`id`) AS max_id FROM photos;");

            ResultSet rs1 = pstmt1.executeQuery();

            //this to to null-proof the logic
            int max_id = 22101985;
            int randomId = 22; // put some real ID just to make sure

            while (rs1.next()) {
                max_id = rs1.getInt("max_id");
            }

            Random randomGenerator = new Random();
            randomId = randomGenerator.nextInt(max_id);

            PreparedStatement pstmt2 = con
                    .prepareStatement("SELECT * FROM photos WHERE `id` >= ? ORDER BY `id` ASC LIMIT 1");

            pstmt2.setInt(1, randomId);

            ResultSet rs = pstmt2.executeQuery();
            PhotoBean tmpPhoto = new PhotoBean();
            while (rs.next()) {
                tmpPhoto.setPhotoId(rs.getString("photo_id"));
                //... more
            }

            //ps: you can use just one pstmt and it will be no problem as its executed separately but i am just too lazy
            pstmt1.close();
            pstmt2.close();
            releaseConnection(con);

            return tmpPhoto;

        } catch (Exception e) {
            //... more

    }

I use CI 1.7.2

This code should resides in model class. In short, this is the model you need to consider,

<?php
class Data_model extends Model {
/* we are going to use this getSome for example purpose */
	function getSome() {

/*all the code displayed below need to be inserted here */

/* standard row thing... its better to put this loop inside macros */
		if($q->num_rows() > 0) {
			foreach ($q->result() as $row) {
			    $data[] = $row;
			}
		return $data;
		}
	}
}

assuming q is the query and there is data table available somewher with id, title, author and contents column

First way, write yourself

$q = $this->db->query("SELECT * FROM data");

Second way, same sql query but with prepared statement to prevent sql injection.

 $sql = "SELECT title,author,contents FROM data WHERE id = ? AND author =?";
 $q = $this->db->query($sql, array(4,"Author two") );

Third way, use active record way-ish

$q = $this->db->get('data');

Fourth way, same like third way but with additional parameters specifying which column to retrieve. Should be faster than the second one.

$this->db->select('title, contents');
$q = $this->db->get('data');

Fifth way, same like fourth way but with more parameters specified separately for easier reading.

$this->db->select('title, contents');
$this->db->from('data');
$this->db->where('id', 1);
$q = $this->db->get();