CODEWITHSUNDEEP
mysqlmemcached
Dr_movi
Dr_movi

Reputation: 45

best practice using memcached with mysql search query?

i've friendship_request database table , will if this table have say a million record when i'll try to search for requests concerning tom for example and actually there is no friendship request for tom in these million record so which is better: to index to in memcache when some one send request to tom and then first try to check memcache for tom so if present go to database and make a search query. OR to make a search query directly without first check memcache which is faster? thanks

Upvotes: 0

Views: 1713

Answers (1)

emcconville
emcconville

Reputation: 24419

Checking against Memcache first will be faster. Like the name applies, items are stored in memory, and will have less I/O than a database look up. However, Memcached will evict any data if the server's RAM becomes full. For this reason, it would be unwise to assume that any data put in Memcache will still be there. Even before the item's time has expired.

Most Memcache wrappers and API's will return FALSE, or NOT_FOUND. So your application will just need to identify if your requested items was stored, and if not, check the database. Upon completing the database search, simply record in Memcached the results or no results. This will prevent your application from re-running the same search again.

Here's a quick interaction with Memcached

shell ~> telnet localhost 11211
tel ~> get tom
       NOT_FOUND

tel ~> set tom 0 86400 5
    ~> 1,2,3
       STORED

tel ~> get tom
       VALUE tom 0 5
       1,2,3
       END

And a quick little PHP class example 

<?php
class FriendshipRequest extends Memcache {

    const KEY_PREFIX = 'fr_' ; // Use constant prefix to create namespace
    const FLAG = 0;            // Flag for memechache storage option
    const EXPIRE = 86400;      // Store for one day

    public function __construct() {
    $this->addServer('localhost',11211);
    }

    public function __destruct() {
        $this->close();
    }

    /**
     * Find a name
     *
     * First check memcached. If not found,
     * search database, and store results
     * in a JSON string
     *
     * @param string $name
     * @return array
     */
    public function find($name) {
            $key = $this->toKey($name);
            $results = $this->get($key);
            if( $results === false ) {
                    $results = $this->databaseSearch($name);
                    $this->set($key,json_encode($results),self::FLAG,self::EXPIRE);
            } else {
                    $results = json_decode($results);
            }
            return $results;
    }

    /**
     * Run a database search, and return array of matching ID's
     * @param string $name
     * @return array
     */
    private function databaseSearch($name) {
            $sql = sprintf('SELECT `friend_id` FROM `friendship_request` WHERE `friend_name` LIKE "%s"', mysql_real_escape_string($name));
            $matches = array();
            // Database connection omitted
            $results = mysql_query($sql);
            while( $row = mysql_fetch_object($results) ) {
             array_push($matches,$results->friend_id);
            }
            mysql_free_result($results);
            return $matches;
    }

    /**
     * Create a namespace for key
     * @param string $name
     * @return string
     */
    private function toKey($name) {
            return self::KEY_PREFIX.sha1($name);
    }
}

Upvotes: 2

Related Questions