Hi , all as web is taking it's new hieght our 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 you can save your 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.
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. Your 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
Hope this article helps you enjoy optimized and zero load website.
Read more (with all the source codes).