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

    }

This is a quick and painless way to remove duplicate rows from a MySQL database table. It requires no programming or PHP coding whatsoever. Without further ado…

STEP 1

CREATE TABLE new_table as
SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];

STEP 2

DROP TABLE old_table;

STEP 3

RENAME TABLE new_table TO old_table;

STEP 4
DO NOT FORGET TO RESET THE PRIMARY KEY AND FOREIGN KEY AGAIN

from

http://www.justin-cook.com/wp/2006/12/12/remove-duplicate-entries-rows-a-mysql-database-table/

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();

This below code will not work since java.sql.Date is not compatible with java.util.Date and hence pstmt.setDate(2, tweetBean.getCreatedAt()); gives exception error.

FYI, created_at column is of datetime type and tweetBean.getCreatedAt() will return java.util.Date

            PreparedStatement pstmt = con
                    .prepareStatement("INSERT INTO `crawler`.`tweets` (`tweet_id`, `created_at`, `from_user`, `from_user_id`, `profile_image_url`, `tweet_source`, `text`, `latitude`, `longitude`) VALUES (?,?,?,?,?,?,?,?,?)");

            pstmt.setLong(1, tweetBean.getId());
            pstmt.setDate(2, tweetBean.getCreatedAt());
            ...

            int count = pstmt.executeUpdate();

            pstmt.close();
            releaseConnection(con);
 

solution

            PreparedStatement pstmt = con
                    .prepareStatement("INSERT INTO `crawler`.`tweets` (`tweet_id`, `created_at`, `from_user`, `from_user_id`, `profile_image_url`, `tweet_source`, `text`, `latitude`, `longitude`) VALUES (?,?,?,?,?,?,?,?,?)");

            pstmt.setLong(1, tweetBean.getId());
            java.util.Date tempDate = tweetBean.getCreatedAt();
            java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String tempDateString = sdf.format(tempDate);
            pstmt.setString(2, tempDateString);
            ...

            int count = pstmt.executeUpdate();

            pstmt.close();
            releaseConnection(con);
 

This is weird since it means setDate could not do the automatic conversion, forcing me to use setString instead.

ps: this is some of the rare occasion where I do need to write my sql statement… so pardon the alay-ness