MyLyricsFinder.com: Caching MySQL Queries
Filed Under (General) by Wenbert on 28-10-2007
Tagged Under : cache, MySQL, Web Development, Zend Framework, zend_cache
It is Saturday night and it is raining like it hasn’t rained in weeks. So I decided to spend the night to work on my pet project: MyLyricsFinder.com.
It uses the Zend Framework. The latest addition tonight is Zend_Cache. I added the Top 10 Songs and Artist list in the frontpage - so caching it would make sense. Without the cache, every user that will visit the site will have to query the database. Getting the top 10 list from 200K+ rows is pretty heavy on the resources. Not to mention the overhead it will cause on the user’s side. So I came up with the code below:
-
-
//this code snippet is part of my indexAction() in my IndexController
-
-
//for the cache
-
$frontendOptions[‘lifetime’] = 3600;
-
$backendOptions[‘cache_dir’] = Zend_Registry::get(‘cache_dir’);
-
-
// getting a Zend_Cache_Core object
-
$cache = Zend_Cache::factory(‘Core’, ‘File’, $frontendOptions, $backendOptions);
-
-
// see if a cache already exists:
-
if(!$result_pop_songs = $cache->load(‘pop_songs’)) {
-
//get most popular songs
-
$dbAdapter = Zend_Registry::get(‘dbAdapter’);
-
$sql = ‘SELECT
-
s.song_title,
-
s.song_slug,
-
s.song_counter,
-
at.artist_slug
-
FROM songs_table s
-
LEFT JOIN album_table al ON s.album_id=al.album_id
-
LEFT JOIN artist_table at ON al.artist_id=at.artist_id
-
WHERE s.song_counter>0 ORDER BY s.song_counter DESC LIMIT 10′;
-
$this->view->pop_songs = $dbAdapter->fetchAll($sql);
-
$cache->save($this->view->pop_songs, ‘pop_songs’);
-
} else {
-
// cache hit! shout so that we know
-
//echo "This one is from cache!\n\n";
-
$this->view->pop_songs = $result_pop_songs;
-
-
}
In addition to the Top 10 List in the frontpage, I have also cached the list of Artist per Letter and the List of Albums per Artist. I have reduced the number of database requests significantly - although I don’t know how much - I can tell the difference in load time with and without the cache.
Code Walkthough
-
-
$frontendOptions[‘lifetime’] = 3600;
-
$frontendOptions[‘automatic_serialization’] = true;
-
$backendOptions[‘cache_dir’] = Zend_Registry::get(‘cache_dir’);
-
$cache = Zend_Cache::factory(‘Core’, ‘File’, $frontendOptions, $backendOptions);
This part of the code sets up the Zend_Cache. Most likely, yours will be similar to this. Except for the line with the get(’cache_dir’). I have set up Zend_Registry::get(’cache_dir’) in my Bootstrap file to “../tmp/”
-
-
if(!$result_pop_songs = $cache->load(‘pop_songs’)) {
The line above checks if a cache for ‘pop_songs’ already exists. If not, then it will query the database with the code below:
-
-
$dbAdapter = Zend_Registry::get(‘dbAdapter’);
-
$sql = ‘SELECT
-
s.song_title,
-
s.song_slug,
-
s.song_counter,
-
at.artist_slug
-
FROM songs_table s
-
LEFT JOIN album_table al ON s.album_id=al.album_id
-
LEFT JOIN artist_table at ON al.artist_id=at.artist_id
-
WHERE s.song_counter>0 ORDER BY s.song_counter DESC LIMIT 10′;
-
-
$this->view->pop_songs = $dbAdapter->fetchAll($sql);
-
-
$cache->save($this->view->pop_songs, ‘pop_songs’);
The last line will save the query into the cache. So the next time a user will come, the ‘pop_songs’ cache will already exist in the cache and the code below is executed:
-
-
$this->view->pop_songs = $result_pop_songs;
