Cached Queries and Pagination

[eluser]doehoe[/eluser]
Hi,
I intend to use Flexigrid either with my own implementation in CodeIgniter or with the "Flexigrid Implemented in CodeIgniter" project to show the results of quite a complex MySQL query.

Flexigrid paginates results and as far as I've tested, each time the user moves to a page, an AJAX call is made to the server to retrieve those results for the specific page.

Ideally, I'd like for the complex query to be done once (the first time the table is displayed) and its result cached and then for each move to a different page just to use the results from the cache with an applicable limit (for the specific page) placed on the SQL query.

I understand the basic idea of how Database Caching works in CodeIgniter, but if a limit is placed on the query to be cached, does CodeIgniter strip out the limit, perform the query and cache the results to the hard drive and then place the limit on the query? This would be great for pagination I think.

Using either the Database Caching functionality of CodeIgniter or some other method, what would be a recommended way of doing this with CodeIgniter?

[eluser]doehoe[/eluser]
I went looking through the CodeIgniter code to answer my own question about stripping limits - which it seems that CodeIgniter does not do (and probably justifiably so)

At the MySQL level (i.e. language independent) there is the SQL_CACHE option which can be placed on a SELECT query. Although the way queries are indexed is probably similar to CodeIgniter (with a hash of the query). As far as I understand as well the LIMIT part of the query is also part of the hash (both at the CI and MySQL levels).

But it seems somebody made a feature request to MySQL for the kind of functionality I'm looking for (http://bugs.mysql.com/bug.php?id=18707) but a justification is given at the bottom of the request for not allowing for it.

A better context to my question about how to do this best in CodeIgniter would probably help anyone who wants to assist me:

The complex query (which has many joins on multiple tables as well as some sub-queries) is a filtering query to allow for finding records based on many different and relatively complex criteria displayed in a form. The query is built up as criteria are added, removed or changed.
The person using the form will gradually add, remove and change criteria until he/she finds the set of records he/she wants. The user will be able to page through the results as criteria are added, removed and changed. Thus, the query will change quite quickly, but the results need to be displayed after each change.

However, it is possible that the generated query will return 1000's of rows if not 10 000's.

Some of my solutions so far:
1) Screw it, just generate the query again and again for each page and add a limit according to the page number and hope the Indexes I use at the MySQL database will be good enough optimization
2) Use CodeIgniter's Database Caching. This will mean less hits to the MySQL database, but the entire result set will have to be placed in memory just to retrieve a few records which will be on the page (eg. 30 from a result set of 5000)
3) Use a combination of 1 and 2, but instead of placing the limit to the amount of queries required for the page, I will limit the resultset to those required for a few pages (maybe 5) and then when the use moves out of this range of pages, just make a new request to the database for the next 5 pages.

3 seems like the best Engineering approach to me.

[eluser]TheFuzzy0ne[/eluser]
[quote author="doehoe" date="1237062953"]I understand the basic idea of how Database Caching works in CodeIgniter, but if a limit is placed on the query to be cached, does CodeIgniter strip out the limit, perform the query and cache the results to the hard drive and then place the limit on the query? This would be great for pagination I think.[/quote]
What if your table contains millions of rows? A query cache should represent only a single query, otherwise you'll need to search the file, in which case you would have just been better off just querying the database.

[eluser]TheFuzzy0ne[/eluser]
Can you not have a table to hold the cached results? You could cache results for a set amount of time, and probably generate a key for each user.

[eluser]bretticus[/eluser]
I got the impression that CI query caching was only for reusing bits of a SQL query and not caching records. Why not use app caching to store your results? (if you have millions of results, make sure you have lots of memory!)

Here is a quick excerpt from one of my CI models:

Code:

<?php
// store results in array so that we can more easily cache.
$results = array();
// get the compiled query (private function hack but works fine for me)
$compiled_query = $this->db->_compile_select();
// check for hashed query key in apc cache
if ( apc_fetch(md5($compiled_query)) ) {     // get results from memory
    // clear compiled query
    $this->db->_reset_select();
    $results = apc_fetch(md5($compiled_query));
} else {                                    // call database again for results
    $query = $this->db->get();
    // build array with database results
    foreach ($query->result_array() as $row)
        $results[] = $row;
    // store results in app cache
    apc_store(md5($compiled_query), $returned, APC_CACHE_TTL);
    $query->free_result();
}
?>

[eluser]doehoe[/eluser]
[quote author="bretticus" date="1237085019"]I got the impression that CI query caching was only for reusing bits of a SQL query and not caching records.[/quote]

Thanks for your code, but the query caching is definitely for caching records. The records are serialised and stored in ASCII files in whatever directory you specify using a specific directory and file naming scheme.

Btw, I have decided to go with my 3rd option (from my first post).

[eluser]bretticus[/eluser]
[quote author="doehoe" date="1237152967"][quote author="bretticus" date="1237085019"]I got the impression that CI query caching was only for reusing bits of a SQL query and not caching records.[/quote]

Thanks for your code, but the query caching is definitely for caching records. The records are serialised and stored in ASCII files in whatever directory you specify using a specific directory and file naming scheme.

Btw, I have decided to go with my 3rd option (from my first post).[/quote]

Good to know. I am relatively new to CI and only browsed the database caching as an afterthought for my first project done using CI (I just finished my second )

I know pagination really changes the equation a lot, but I would use app caching over ascii files in a heartbeat if the situation presented that route to me.

Good luck.

[eluser]Nathan Pitman (Nine Four)[/eluser]
As a bit of a newb could you explain the difference between app caching and query caching? IT's also worth noting that the query caching borks quite fast at large numbers of records.

I just tested it on an app we have and it keeled over with an out of memory message when trying to render a table of just over 3,000 records. If I turned caching back off CI was able to pull the results out all into 1 page just fine. Obviously having that many records on one page is unusable but that's just an example... I will no doubt be adding paging!

On a related note, have you ever looked at a AJAX powered continual scrolling concept? ie: when the users scrolls to the bottom of the first page of results use AJAX to extend the table and add another 50 results... etc.

BTW, there's a good example fo this here: http://unspace.ca/livefilter/results/pNbgJIwt8s

[eluser]Pascal Kriete[/eluser]

As a bit of a newb could you explain the difference between app caching and query caching

I can't resist.

CI's query caching writes to disk, and when done in large amounts this can actually hurt performance as the disk i/o goes beyond what the server can cope with. It's the first time I've heard the name 'app caching', but essentially what bretticus' code does is cache to memory, meaning that the data is persistent across page loads without having to touch the disk.

Obviously memory isn't unlimited so as you keep adding data, the oldest starts to be evicted. Thus it just serves as a result buffer - you'll still want a db in the background.

You get two options.
1. APC, which also serves as an opcode cache - so it's good to have anyways, but is limited to local memory (use for configuration and such..).
2. Memcached - distributed across servers, scales without much effort - just start another client (great for query results/reducing db load).

It depends a lot on your app. APC will work fine until you hit critical mass. Easiest solution is to have a library that supports both so you can simply flip a switch when the time comes and you need more space.