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?