Using the same MySQL query with all users without having to reconnect to the database [closed]

Question

I'll try to be simple, so what i need is, perform a MySQL query (selecting records from a table) let's say ill produce the query with cron jobs or whatever every 5 minutes, and save this query or results to a file or a global variable if it's possible, and then serving it to all clients (i.e. using the same query to all users, without having to reconnect to that database.) Now i know some of you will tell me to use cache, but i searched for it, and if the table changes there will be new cache, and my table is constantly changing, now i don't care if the table has changed, i just want to serve the same query to all clients. Is that possible? your recommendations please. Thanks.

The cache expires automatically when the table is modified (inserts, updates, delete's, etc). And my table is constantly changing, so is it possible to save a cache even if the table has been modified? or should i look for a different solution?

Andes

Have you looked into caching solutions? – Athey

doing repetitive identical queries is not unusual - are you have specific issues? mysql is doing some caching by default. – Bombardon

Yea, i mentioned that already in my question, but as I've said, my table is constantly changing and the cache will be deleted if the table has been updated. – Andes

@Dagon, my specific issues is that i use the same query with all users but i manipulate it differently with every user. – Andes

and so? what's the problem with that? – Bombardon

The problem is, i dont want to reconnect to the database everytime to use the same query to save connections. so i want to save the query and reuse it over and over again with each different user – Andes

can you provide a short code example of what your currently doing. – Bombardon

Sorry, i dont think i can, because there are to many files working with each other, so basically what i need is, perform a MySQL query and save it, then reuse it without having to reconnect to the database. – Andes

well that's called caching, which in the original question you where against. so well i'm lost. – Bombardon

@Dagon i updated my question, read the last part plz. thanks. – Andes

How about just caching. memcached can be a starting point. you can reset it anytime there is a change in the database – Underprop

Could you show me how to use memcached to store a MySQL query, or do know any useful articles about that? – Andes

Answer 1

If the result set isn't too big, you could cache it into a generated script like so:

file_put_contents(
    'mycache.php', 
    '<?php return ' . var_export($results, true) . ' ?>'
);

Then to use it:

$results = include 'mycache.php';

Heteroousian

thank you very much. Your method gave just what i need. – Andes

Answer 2

If you are always using same way (function, class) to perform database read and write,
you can always putting a cache expireation after each action

for example:

function add() // update, delete
{
  if (added) $this->expire_cache();
}

function search()
{
  if (results) $this->set_cache(results);
}

function expire_cache()
{
  // do expire or purge cache
}

function set_cache( $db_results )
{
  // saving the database result OR computed result into cache
  // memcache, redis, disk cache or anything
}

However, the purpose of having cache is to serve the request without having to get expensive resource like database query,
but if you are required to update very frequently, then the cache implementation is not so useful

memcache example : http://php.net/manual/en/book.memcache.php

you would need to compile memcache module, setup & start a memcache server in order to use memcache

Calibrate

Yea because my table is getting updated very frequently :/ so i cant use MySQL cache. And the problem is, you can only install memcache on detected servers. – Andes