Drupal - How do I cache the result of db_query()?

Doesn't mattter for caching if you use views or db_query(). Caching works always the same, how the data is fetched when the cache misses is completely up to you.

1. Build a cache ID to identify your cache entry. Can be a simple, hardcoded string or something complex based on arguments and so on.
2. Check if can load from the cache.
3. If not, rebuild data and put it into the cache with the desired expiration time.

To see some examples, you can look at the functions that use cache_get(), for example variable_initialize().

If your function is called multiple times then you probably want to combine it with a static cache, see for example archiver_get_info(). And if the data rebuilding is really slow, you can prevent it from happening multiple times by using the locking framework as variable_initialize() does.

Note that caching a single query only makes sense if it's a slow one, because a cache_get() is also a db query unless you use an alternative cache backend like Memcache.

And lastly, Views has caching already built-in and can be configured in your view. So that might be an option as well.

I don't think the DB layer has any built in caching mechanism (though I might be wrong), but you could make use of the default cache API.

This is just a basic example that will cache the results of a query to get nodes of a certain type:

function MYMODULE_get_nodes_by_type($type) {
  // Setup a cache ID
  $cid = 'MYMODULE:node_types:' . $type;

  // If a cached entry exists, return it
  if ($cached = cache_get($cid)) {
    return $cached->data;
  }

  // Otherwise load the data
  $data = db_query('SELECT * FROM {node} WHERE type = :type', array(':type' => $type))->fetchAll();

  // And cache it
  cache_set($cid, $data, 'cache', strtotime('+6 days'));
}

In addition to the standard cache_set / cache_get mechanism that Drupal provides, if you are using MySQL as your database, then you can enable the query cache, which can cache the results of views, or any other database queries, transparently. mysqltuner can aid in figuring out good values for the cache size.

Just note that if you are doing a lot of writing to the database, then query caching becomes less effective because of the way the cache invalidation strategy works (a write to a table invalidates all entries that SELECT FROM or JOIN that table).

There is also a caching mechanism for PostgreSQL, but I don't have direct experience with it.