Received: from icebox.venux.net (icebox.venux.net [216.120.166.10]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA43988 for ; Thu, 18 May 2000 11:47:49 -0400 (EDT) (envelope-from mitch@venux.net) Received: from servbox.venux.net (matrix.venux.net [216.120.166.5]) by icebox.venux.net (Postfix) with ESMTP id 52DFA26208 for ; Thu, 18 May 2000 11:51:30 -0400 (EDT) Received: from doot (doot.venux.net [10.0.0.65]) by servbox.venux.net (Postfix) with SMTP id 3A5262BD82 for ; Thu, 18 May 2000 11:50:20 -0400 (EDT) Message-ID: <014b01bfc0e0$9fb352c0$4100000a@venux.net> From: "Mitch Vincent" To: Subject: LIKE and regex Date: Thu, 18 May 2000 11:49:17 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 X-Archive-Number: 200005/55 I'm wondering if LIKE uses regex internally.. I'm trying to find the quickest / fastest way to search for any occurance of a given string in a stored field. I tried both of these : query: select * from applicants where firstname LIKE '%mitch%'; ProcessQuery ! system usage stats: ! 0.407890 elapsed 0.104924 user 0.139892 system sec ! [0.133769 user 0.149507 sys total] ! 6/13 [7/13] filesystem blocks in/out ! 0/60 [0/425] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [3/3] messages rcvd/sent ! 6/15 [8/26] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 1301 read, 0 written, buffer hit rate = 41.21% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written query: select * from applicants where firstname ~ lower('mitch'); ProcessQuery ! system usage stats: ! 0.234621 elapsed 0.101358 user 0.124995 system sec ! [0.287402 user 0.242496 sys total] ! 0/5 [160/10] filesystem blocks in/out ! 0/0 [0/434] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [4/4] messages rcvd/sent ! 0/35 [153/55] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 1243 read, 0 written, buffer hit rate = 0.24% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written While there is quite a load on that box right now (doing a big CLUSTER) I tried the above queries 3 times, the above is the result of both 3rd-round queries.. It looks like LIKE is faster to me.. Any comments? Thanks! - Mitch "The only real failure is quitting."