Using cachedwithin attibute inside cfquery

Question

When you use the cachedwithin attribute in a cfquery how does it store the query in memory. Does it store it by only the name you assign to the query? For example, if on my index page I cache a query for an hour and name it getPeople will a query with the same name on a different page (or the same page for that matter) use the cached results or does it use some better logic to decide if it is the same query?

Also, if there is a variable in your query does the cache take into account the value of the variable?

Answer 1

From http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html

To use cached data, current query must use same SQL statement, data source, query name, user name, password.

So those are the 'keys' that "decide if it is the same query"

variable? yes, as long as you use <cfqueryparam>

Answer 2

It's not only the name -- it's the exact query you're running.

<cfquery name="getPeople" cachedwithin=".5" ...>
select name from employee order by name
</cfquery>

If you invoke this same query anywhere else in your app, you'll get the cached version if it's within half a day of the first query. But these will hit the database for fresh data:

<!--- Different name, same SQL: A new cached query --->
<cfquery name="getEmployees" cachedwithin=".5" ...>
select name from employee order by name
</cfquery>

<!--- Different SQL, same name: Redefining the cached query --->
<!--- Note: As pointed out in comments, it's not really overwriting the old query
      of the same name, but making a new one in the cache. The first one by the
      same name is still in the cache, waiting for eviction. --->
<cfquery name="getPeople" cachedwithin=".5" ...>
select name from employee order by name desc
</cfquery>

And yes, it does take a variable into account. If you use cfqueryparam -- which you should be doing -- your database will cache the query plan, but even using cachedwithin, each query with a changed parameter will be treated as different from a query caching perspective. Note that this means if you use cachedwithin on a query that runs many times with different parameters, you'll be flooding your query cache with queries that have low cache hit rates.