X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6DA15329DC4 for ; Mon, 27 Sep 2004 06:03:30 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 10252-03 for ; Mon, 27 Sep 2004 05:03:26 +0000 (GMT) Received: from sue.samurai.com (sue.samurai.com [205.207.28.74]) by svr1.postgresql.org (Postfix) with ESMTP id 97390329D83 for ; Mon, 27 Sep 2004 06:03:25 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by sue.samurai.com (Postfix) with ESMTP id 861803F2F; Mon, 27 Sep 2004 01:03:25 -0400 (EDT) Received: from sue.samurai.com ([127.0.0.1]) by localhost (sue.samurai.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 71755-01-6; Mon, 27 Sep 2004 01:03:24 -0400 (EDT) Received: from localhost (unknown [61.88.101.19]) by sue.samurai.com (Postfix) with ESMTP id A68E53EFD; Mon, 27 Sep 2004 01:03:23 -0400 (EDT) Subject: Re: Caching of Queries From: Neil Conway To: Aaron Werman Cc: Scott Kirkwood , pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain Message-Id: <1096261381.25688.741.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Mon, 27 Sep 2004 15:03:01 +1000 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at mailbox.samurai.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/275 X-Sequence-Number: 8403 On Thu, 2004-09-23 at 07:43, Aaron Werman wrote: > MySQL stores a statement with its results. This is optional and when a > client allows this type of processing, the SQL is hashed and matched to the > statement - and the stored *result* is returned. The point is that a lot of > systems do lots of static queries, such as a pick list on a web page - but > if the data changes the prior result is returned. This (plus a stable jdbc > driver) was the reason MySQL did well in the eWeek database comparison. I think the conclusion of past discussions about this feature is that it's a bad idea. Last I checked, MySQL has to clear the *entire* query cache when a single DML statement modifying the table in question is issued. Not to mention that the feature is broken for non-deterministic queries (like now(), ORDER BY random(), or nextval('some_seq'), and so on). That makes the feature close to useless for a lot of situations, albeit not every situation. -Neil