X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E7760D1F212 for ; Wed, 5 May 2004 14:37:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 57624-09 for ; Wed, 5 May 2004 14:36:43 -0300 (ADT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0F40AD1F1C1 for ; Wed, 5 May 2004 14:31:59 -0300 (ADT) Received: from [134.22.70.11] (dyn-70-11.tor.dsl.tht.net [134.22.70.11]) by tht.net (Postfix) with ESMTP id 4AB1B76B38; Wed, 5 May 2004 13:32:02 -0400 (EDT) Subject: Re: LIKE and INDEX From: Rod Taylor To: Jie Liang Cc: Postgresql Performance In-Reply-To: References: Content-Type: text/plain Message-Id: <1083778304.60668.3.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Wed, 05 May 2004 13:31:45 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/41 X-Sequence-Number: 6872 > but if I use: > select url from urlinfo where url like 'http://%.lycos.de'; > it won't use index at all, NOT good! > is there any way I can force secon query use index??? create index nowww on urlinfo(replace(replace(url, 'http://', ''), 'www.', ''))); SELECT url FROM urlinfo WHERE replace(replace(url, 'http://', ''), 'www.', '') = 'lycos.de' AND url LIKE 'http://%.lycos.de' ; The replace() will narrow the search down to all records containing lycos.de. Feel free to write a more complex alternative for replace() that will deal with more than just optional www. Once the results have been narrowed down, you may use the original like expression to confirm it is your record.