1
\$\begingroup\$

I have an existing MySQL schema without rows and have already made a list of all SQL queries I need. Now I know that Redis is a key-value store, so you can only fetch data by key and save for example arrays as json-strings.

My videos table for example looks like the following:

 CREATE TABLE IF NOT EXISTS `videos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(80) CHARACTER SET utf8 NOT NULL,
`weight` tinyint(4) NOT NULL DEFAULT '0',
`length` varchar(7) NOT NULL DEFAULT '0:00',
`script` text CHARACTER SET utf8 NOT NULL,
`node_id` int(11) NOT NULL,
`filename` varchar(255) CHARACTER SET utf8 NOT NULL,
`filetype` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `title` (`title`),
KEY `filename` (`filename`),
KEY `title_2` (`title`),
KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=159 ;

And I have for example the need to get all videos with a specific node_id, ordered by weight ascending. Now my approach was the following:

Storing queryable data by using tags in a hashed string like table_name + pos ('all|first|last') + condition_name + limit + order, e.g. hashing an imploded array, like this in php:

<?php
$key = md5(implode(", ", array('video', 'all', 'node_id = 5', 'weight asc')));

Now trying to replace the mysql database, I got the problem that when someone wants to write data into the database, I need to store the data in all needed queried conditions, so I need to hard code them in PHP, instead of just saving the data and querying it with SQL. I did it in the example like this:

public function allVideosByNodeId($input) {
   $allVideosByNodeId_key = md5(implode(", ", array('video', 'all', 'node_id = '.$input['node_id'], 'weight asc')));
   $currentVideosByNodeId = $this->redis->get($allVideosByNodeId_key);
   if (!empty($currentVideosByNodeId)) {
      $allVideosByNodeId_value = json_decode($currentVideosByNodeId);
   }
   $allVideosByNodeId_value[] = $input; //append with input data array
   $this->redis->set($allVideosByNodeId_key, json_encode($this->sortByWeightAsc($allVideosByNodeId_value)));
}

This is a working approach, but it's my first trial with redis and I've no experience with it since I always used SQL Databases. If you have only one write to database it is still fast, but what happens if I i need to store twenty models like this writing new data becomes maybe slow. Does anyone have some tips for a better implementation of such queried results?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.