Home > Archive > PHP DB > March 2007 > Re: [PHP-DB] Caching query results in html pages
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [PHP-DB] Caching query results in html pages
|
|
| Amit Patel 2007-03-15, 3:59 am |
| I believe a much better solution would be to use MySQL Query Cache.
http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
Use it wisely and there is a lot of performance gain. Donot simply enable
cache for all sql statements.
Amit.
On 3/13/07, Micah Stevens <micah@raincross-tech.com> wrote:
>
> This may work, although I just made it up. I can see already that you'd
> have some problems with multiple scripts running at once. If a script
> opens the cache, then a second script saves new cache information before
> the first script saves it's data, the first script would overwrite the
> second script's update.
>
> Maybe instead of a read at the beginning of script execution, a read at
> the beginning of each query would be better?
>
> Save cache results to a file:
>
> <?
> // script start
> $querycache = unserialize(file_get_contents("query.cache"));
>
> /*
> $querycache format:
>
> array('sql' => array(), 'result'=>array(), 'time'=>array());
>
> For each sql statement 'sql' you have a result array.
> */
> // run all your queries through a function:
> function query($sql) {
> $cached = array_search($sql, $querycache['sql'])
> // check to see if the result is old
> if (time() - $querycache['time'][$cached] > $max_time) {
> unset($querycache['time'][$cached]);
> unset($querycache['sql'][$cached]);
> unset($querycache['result'][$cached]);
> $cached = false;
> }
> if ($cached) {
> return $querycache['result'][$cached];
> } else {
> $result = mysql_query($sql);
> $querycache['sql'][] = $sql;
> $index = array_search($querycache['sql']);
> $querycache['time'][$index] = time();
> while ($r = assoc($result)) {
> $querycache['result'][$index][] = $r;
> }
> // save cache for other scripts
> file_put_contents('query.cache', serialize($querycache));
> return $querycache['result'][$index];
> }
> }
>
> ... It's actually kind of a complicated process now that I'm thinking
> about it.
>
> -Micah
>
>
>
>
>
> On 03/13/2007 06:20 AM, Vincent wrote:
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
| |
|
| Amit Patel wrote:
> I believe a much better solution would be to use MySQL Query Cache.
> http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
>
> Use it wisely and there is a lot of performance gain. Donot simply enable
> cache for all sql statements.
Maybe if you have complete control over the server you can do this.
If you're on a shared host, you have no hope - anyone else's queries can
knock yours out of the cache and you're back to square 1.
Depending on the queries (and whether they are indexed or not) it might
not even be worth worrying about attempting to cache - it all depends on
the application really (whether the queries are needed, how it handles
the results etc).
--
Postgresql & php tutorials
http://www.designmagick.com/
|
|
|
|
|