2

Is there a way to cache results of a mysql query manually to a txt file?

Ex:

$a=1;
$b=9;
$c=0;
$cache_filename = 'cached_results/'.md5("$a,$b,$c").'.txt';
if(!file_exists($cache_filename)){
    $result = mysql_query("SELECT * FROM abc,def WHERE a=$a AND b=$b AND c=$c");
    while($row = mysql_fetch_array($result)){
        echo $row['name'];
    }
    // Write results on $row to the txt file for re-use
}else{
    // Load results just like $row = mysql_fetch_array($result); from the txt file
}

The original query contains more WHEREs and joins that uses multiple tables. So, Is this possible? If so, please explain.

Thank you, pnm123

Gergo Erdosi
  • 40,904
  • 21
  • 118
  • 94
Prasad N
  • 543
  • 4
  • 10
  • 22
  • Unless your real query is very involved and not indexed very well, I would think your caching would be slower. Remember, at worst case, mySQL is also getting your results from the filesystem. – mkoistinen Sep 11 '10 at 18:56
  • I've indexed all required fields. So I thought it will be much faster by caching results of the query as MySQL have to run it only once since there are no change on the query. – Prasad N Sep 11 '10 at 18:58
  • 1
    Mysql has it's own query cache. And processing queries is Mysql job. And premature optimization is the root of all evil. – Your Common Sense Sep 11 '10 at 19:12
  • but sometimes we can do it's job better than the way it is doing. – Prasad N Sep 11 '10 at 20:12

1 Answers1

3

If you're sure that your data has a long time-to-live, you can certainly cache data by saving it temporarily to a text file.

if (!file_exists($cachefile)) {
    // Save to cache
    $query=mysql_query('SELECT * FROM ...');
    while ($row=mysql_fetch_array($query))
        $result[]=$row;
    file_put_contents($cachefile,serialize($result),LOCK_EX);
}
else
    // Retrieve from cache
    $result=unserialize(file_get_contents($cachefile));
foreach ($result as $row)
    echo $row['name'];

Although using APC, MemCache, or XCache would be a better alternative if you consider performance.

bcosca
  • 17,371
  • 5
  • 40
  • 51
  • What if there are two or more rows/results? What do I need to use instead while($row=mysql_feth_array($result)){ ? – Prasad N Sep 11 '10 at 19:04
  • then instead of `serialize($row)`, use `serialize($result)` – bcosca Sep 11 '10 at 19:19
  • Sorry. I still didn't get the way of retrieving from cache. By the way, I've edited the question. So, how can I print multiple results by retrieving data from cache? just like while(){ echo; } – Prasad N Sep 11 '10 at 19:27