List:Internals« Previous MessageNext Message »
From:Sergei Golubchik Date:November 12 2009 7:02pm
Subject:Re: query cache extension
View as plain text  
Hi, Amila!

On Nov 11, Amila Liyanaarachchi wrote:
> Hi,
> I'm thinking of extending the query cache to deal with following
> scenarios,
> 1. stop invalidating the cache entries when updates take place for
> columns which are not in the select list of the cached queries
> 2. when a second query contains a subset of columns from a first query
> (which has cached results) obtain the results from the cache, this
> will not happen due to the difference in the query, but since the
> select list of the second query is a subset of the select list of the
> first query (and the where clause should be identical) we can obtain
> the results from the cache.
> 3. identify the queries with identical select lists but having column
> names in different orders and process them as identical queries and
> take the advantage of query cache.
> Since I'm a beginner in this area I would like to know whether this is
> doable and any problems that may arise ....

Everything's doable, the question is only how much work it'll require.

1. That's reasonably easy, but it will produce incorrect results, if
imeplented exactly as you described. For example:

   SELECT a FROM t1 WHERE b=5;
   UPDATE t1 SET b=6;

as you see, the second UPDATE does not update any columns in the select
list, but it definitely affects the results of the whole SELECT. That
is, you need to take WHERE condition into account. And probably GROUP
BY, ORDER BY, HAVING. And triggers, and foreign keys.

2. and

The problem here is that query cache caches query result in the form of
network packets, basically what you would see with a network sniffer
like tcpdump.

To do any transformations of the cached entries you need either to
change the format of cached data - that is, store not the network
packets, but higher level structures. Which will inevitably make query
cache responses slower, as you'll need to convert your structures to
network packets on every cache hit. But one cache entry will be able to
answer all queries with any subset of columns in any order.

Or you can of course do transformations on the network packets, for this
you'll need to parse them (similar to what libmysqlclient does) massage
the data (e.g. remove columns) and assemble network packets again.

Regards / Mit vielen Grüßen,

   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
/_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München 161028
       <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Häring
query cache extensionAmila Liyanaarachchi11 Nov
  • Re: query cache extensionSergei Golubchik12 Nov
    • Re: query cache extensionAmila Liyanaarachchi13 Nov
      • Re: query cache extensionSergei Golubchik13 Nov
        • Re: query cache extensionAmila Liyanaarachchi11 Dec
          • Re: query cache extensionRick James16 Jan
Re: query cache extensionAmila Liyanaarachchi20 Jan
  • Re: query cache extensionSergei Golubchik20 Jan