CODEWITHSUNDEEP
phpmysqlkendo-uimemcachedkendo-grid
imperium2335
imperium2335

Reputation: 24122

Caching MySQL results with Memcache and sorting/filtering cached results

To help everyone understand what I'm asking I put forward a scenario:

I have user A on my web app.

There is a particular page which has a table that contains information that is unique to that user. Let's say it is a list of customers that only show for user A because user A and these customers are in region 5.

Other users are assigned to different regions and see different lists of customers.

What I would like to do is cache all of the results for each users list. This isn't a problem as I can use:

    $MC = new Memcache;

    $MC->addserver('localhost');
    $data = $MC->get('customers');
    if($data)
    {

    } else {
        $data = $this->model->customersGrid($take, $skip, $page, $pageSize, $sortColumn, $sortDirection, $filterSQL, $PDOFilterParams);
        $MC->set('customers', $data);
    }
    header('Content-Type: application/json');
    return $data;

The challenge now is to somehow convert the SQL filter syntax that comes from my users table into a function that can filter and sort an array ($data is a JSON string that I would turn into an array if that's the right way to go).

Just for reference, here is the array of aliases I use for building the WHERE clause in my statements:

    $KF = new KendoFilter;
    $KF->columnAliases = array(
        'theName' => 'name',
        'dimensions' => 'COALESCE((SELECT CONCAT_WS(" x ", height, width, CONCAT(length, unit)) FROM products_dimensions,
         system_prefs, units_measurement
         WHERE products_dimensions.productId = product.id
         AND units_measurement.id = system_prefs.defaultMeasurementId), "-")',
        'gridSearch' => array('theName', 'basePrice')
    );
    $filterSQL = $KF->buildFilter();

My question is what is a good way to filter and sort memcache data as if it was an SQL query? Or does memcache have something already built in?

Upvotes: 2

Views: 1172

Answers (1)

Markus
Markus

Reputation: 5807

Memcache cannot do this - you can't replace your database with memcache (that is not what it is for), you can only store key => value pairs.

I think a better approach is to store each data for each user in a specific mem cache key.

So for example if user A with $user_id = 123 visits the page:

$data = $MC->get('customers_for_'.$user_id);

This way you only get the customers for user 123.

A more generic approach is to generate a hash for each sql query with it's params (but that might be overkill in most cases). For example if you have a query select ... from ... where a = @a and b = @b with variables $a and $b you could do the following (you must adapt this for kendo of course, but to get the idea):

$query = "select ... from ... where a = @a and b = @b";
# crc32 because it is fast and the mem key does not get too long
$sql_crc = crc32($query.$a.$b);
$data = $MC->get("customers_".$sql_crc);

To rule out (unlikely) hash collisions for different users, you could mix in the user id in the key, too:

$data = $MC->get("customers_for_".$user_id."_".$sql_crc);

BUT: If you start doing this all over the place in your app because otherwise it is too slow, then maybe the problem lies in your database (missing/wrong indexes, bad column definitions, complicated relations, etc.) and time should better be invested in fixing the DB than working around the issue like this.

Upvotes: 1

Related Questions