3

I would like my View not to use MySQL cache as one of the checks in WHERE clause keeps changing with current time (granularity set to second). Caching this query doesn't make much sense. I would like to convey this to MySQL server by rewriting SQL to SELECT SQL_NO_CACHE. I looked at hook_views_query_alter(&$view, &$query) with a hope to achieve it but I couldn't figure out how $query need to be altered for this. Usecases found so far talks only about changing order by and where clause conditions. Any directions pertaining to this will be greatly appreciated. Thanks in advance.

1
  • 2
    Because of the PDO aspect that @googletorp mentions, I wonder if you may get solution on the main SO site?
    – mpdonadio
    Commented Jan 25, 2012 at 14:41

1 Answer 1

2
+50

I don't want to say that this is impossible, but it's very close to be true.

The problem is that Views uses the Drupal SQL builder, so when making a select query, it utilizes the SelectQuery which again uses PDO from PHP. PDO can be used in two ways, either you feed it a query, or you can use it to build a query by instantiating the class and dosing stuff like

$query = ...;
$query->addField(...);
$query->addCondition(...);
$result = $query->execute();

The challenge is that you need to tell PDO that you don't want to make a regular SELECT query but instead an SELECT SQL_NO_CACHE query. I don't know if that is possible of how you would do it if it were. Even if it is possible, you still need to figure out how to do it through all the abstractions of classes created by PDO and Drupal and Views.

Drupal has a hook you can use to alter queries in general which probably will be the easiest route if you can figure out the above: hook_query_alter, alternatively you can subclass views_plugin_query_default defined in Views, where you can add alter how the query is created to add SQL_NO_CACHE. Again PDO and abstractions is the challenge.

If you are using Drupal 6 this should be more simple and you can probably use something like hook_db_rewrite_sql.

1
  • thanks for your reply. I wish we had a simple way to achieve it. I better let the MySQL cache the query for now.
    – Sivaji
    Commented Jan 25, 2012 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.