Andrew et al-
Keep in mind that when you use LIKE you will NOT use an index but you will use a
full table scan instead
Also instead of using IN EXISTS uses Indexes on the master table query..if you have master-heavy or parent-heavy query use EXISTS/NOT EXISTS vs IN/NOT IN
http://archives.postgresql.org/pgsql-performance/2002-12/msg00185.php
HTH, Martin Gainty
From: "Andrew Dixon" <[EMAIL PROTECTED]>
To: "'Stephen E. Bacher'" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
Subject: RE: In Statement Help
Date: Tue, 3 Aug 2004 12:27:25 +0100
MIME-Version: 1.0
Received: from lists.mysql.com ([213.136.52.31]) by mc10-f2.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Tue, 3 Aug 2004 04:34:24 -0700
Received: (qmail 15763 invoked by uid 109); 3 Aug 2004 11:27:54 -0000
Received: (qmail 15680 invoked from network); 3 Aug 2004 11:27:52 -0000
Received: pass (lists.mysql.com: local policy)
X-Message-Info: JGTYoYF78jHHZ0YfQqtzonhDHhk9WWZ4
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: <mysql.mysql.com>
Precedence: bulk
List-Help: <mailto:[EMAIL PROTECTED]>
List-Unsubscribe: <mailto:[EMAIL PROTECTED]>
List-Post: <mailto:[EMAIL PROTECTED]>
List-Archive: http://lists.mysql.com/mysql/170222
Delivered-To: mailing list [EMAIL PROTECTED]
X-ClientAddr: 212.158.226.158
Message-Id: <[EMAIL PROTECTED]>
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcR5S0T6sJmo+y67QJ+555ZtlMPxWgAAYHSA
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
In-Reply-To: <[EMAIL PROTECTED]>
X-MailScanner-Information: Please contact [EMAIL PROTECTED] for more information
X-MailScanner: Found to be clean
X-MailScanner-SpamCheck: X-Virus-Checked: Checked
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 03 Aug 2004 11:34:25.0293 (UTC) FILETIME=[D4356BD0:01C4794D]
Hi.
Thanks Stephen, the works like a dream... Unlike this database which is more
like a nightmare!!!
Best Regards
Andrew.
-----Original Message----- From: Stephen E. Bacher [mailto:[EMAIL PROTECTED] Sent: 03 August 2004 12:16 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: In Statement Help
Andrew Dixon <[EMAIL PROTECTED]> wrote:
>Hi Everyone. > >I require some help with an IN statement I'm trying to get working. I >have inherited a database from someone else in which there is a table >with project information and a field containing which geographic >regions the projects relate. As a single project can relate to multiple >geographic regions the person who created the database create this >field as a varchar field and populated it with a pipe (|) delimited >list of ID's of the geographic regions (ID from another table). An >example of the data in this field is: > >1|5|9|10|12 > >Or > >1 > >Or > >9|5|7 > >I have been asked to write a query to return all the projects for a >single geographic region. This is what I have come up with: > >SELECT project_title >FROM projects >WHERE 9 IN (REPLACE(geo_region,'|',',')) >ORDER BY project
[...]
I would suggest something like
WHERE CONCAT('|',geo_region,'|') LIKE '%|9|%'
- seb
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
_________________________________________________________________
Don’t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
