All web application’s database should be optimized and new techniques are required to handle the large records Here is a concept used by CodeIgniter where we can save our query result and avoid your mysql query execute every time your page loads.
CodeIgniter provides Database Caching Class where you can cache your query results and use later without executing query all again and crawling through tables.
Enabling Caching
1. Caching is enabled in three steps:
2. Create a writable directory on your server where the cache files can be stored.
3. Set the path to your cache folder in your application/config/database.php file.
4. Enable the caching feature, either globally by setting the preference in your application/config/database.php file, or manually as described below.
Once enabled, caching will happen automatically whenever a page is loaded that contains database queries.
The caching works as follows .
The database caching uses text file on your server which will be created after caching is enabled.CodeIgniter’s query caching system happens dynamically when your pages are viewed. When caching is enabled, the first time a web page is loaded, the query result object will be serialized and stored in a text file on your server. The next time the page is loaded the cache file will be used instead of accessing your database. Our database usage can effectively be reduced to zero for any pages that have been cached.
Only the queries which collects data from tables are cached. So only read-type (SELECT) queries can be cached, the queries which write to database are not cached because they don’t generate any results.
So till now, you must be wanted to know how caching files are stored here how it works
CodeIgniter places the result of EACH query into its own cache file. Sets of cache files are further organized into sub-folders corresponding to your controller functions. To be precise, the sub-folders are named identically to the first two segments of your URI (the controller class name and function name).
For example, let’s say
you have a controller called blog with a function called comments that contains three queries. The caching system will create a cache folder called blog+comments, into which it will write three cache files.
If you use dynamic queries that change based on information in your URI (when using pagination, for example), each instance of the query will produce its own cache file. It’s possible, therefore, to end up with many times more cache files than you have queries.
The caching does not work with following query results
num_fields()
field_names()
field_data()
free_result()
Here’s the syntax for database caching :
$this->db->cache_on() / $this->db->cache_off()
Example:
// Turn caching on $this->db->cache_on(); $query = $this->db->query("SELECT * FROM mytable"); // Turn caching off for this one query $this->db->cache_off(); $query = $this->db->query("SELECT * FROM members WHERE member_id = '$current_user'"); // Turn caching back on $this->db->cache_on(); $query = $this->db->query("SELECT * FROM another_table");
Delete Cache: Deletes the cache files associated with a particular page. This is useful if you need to clear caching after you update your database.
$this->db->cache_delete()
Clears all existing cache files
$this->db->cache_delete_all()
Except database optimization, we can try to optimize this way also:
1. Compress HTML output, like this
2. Output Cache function:
$this->output->cache(60); // Will expire in 60 minutes
3. Enable Gzip Compression
$config['compress_output'] = TRUE; // add this code into config.php
Hope this article helps you enjoy optimized and zero load website