Why query cache does not use cache.ordercache

[cindyzqtnew]

I recently look into the implementation of query cache and have some questions.

The query cache is implemented brand new. But why doesn't it use cache.ordercache which is used by range cache?

I know the difference is that each entry might have different size in the query cache while entry size is same in order cache. But query cache has limit of entry size, so we could relatively control the total cache size <= (max(entry size) * # of entry). What's more, the entry size of query cache is just calculated roughly in the current implementation.

I don't see why we have 2 implementations of cache, while the order cache is implemented generally. Is it because of any performance issue, or others?

[ajwerner]

I think the reason is that the OrderedCache allows for ordered iteration of its contents whereas the UnorderedCache only allows for point interactions. It has nothing to do with the underlying LRU nature of the cache nor its caching policies; the Ordered nature is with regards to the sorted ordering of the items which are present in the cache. OrderedCache keys are expected to implement llrb.Comparable. Below see a list of operations not offered by the UnorderedCache.

[cindyzqtnew]

@ajwerner thanks for the explanation. BTW, is there any plan to make the query cache support queries with same pattern after distracting the constants? Currently, if I execute "select * from A where a = 1" and then "select * from A where a = 2", the second query won't hit the cache. Running the same query including the constants multiple times is not quite common. It makes the query cache not very useful in the practical business scenario.

[ajwerner]

The usual pattern for this would be to use a placeholder and to bind a parameter at runtime. Like, the query would be select * from A where a = $1 and then you could bind either 1 or 2 to $1. We'd use the query cache as the basis for the query and then we'd re-optimize for the given parameter values. Different placeholder values can warrant different plans based on statistics.

[cindyzqtnew]

prepare + execute is not a good option, since we are using an instant connection instead of long-live connection, in which case it requires to pre-definite (prepare) in every new connection and it's not acceptable. I understand we take account into constants in order to optimize the query, but for some simple query like querying by primary key or secondary index, we don't need to consider statistics to optimize the query, right?

[ajwerner]

I understand we take account into constants in order to optimize the query, but for some simple query like querying by primary key or secondary index, we don't need to consider statistics to optimize the query, right?

The optimizer has a fast path for this. If the query is one of those simple ones, no re-optimization occurs.

prepare + execute is not a good option, since we are using an instant connection instead of long-live connection, in which case it requires to pre-definite (prepare) in every new connection and it's not acceptable.

You don't need to do an explicit prepare+execute to use placeholders. In general the driver will pipeline sending all of the relevant wire protocol messages. In practice it shouldn't really be any extra overhead and often will be cheaper from a client perspective because you won't need to format strings. What driver are you using?

[cindyzqtnew]

Got you. I am using JDBC for testing. I know the api you mentioned and I'll try.