Nope, You need a key on your SOURCE column to help this query respond. ALTER TABLE email_table ADD KEY(SOURCE);
Then, try again. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rob Kudyba <[EMAIL PROTECTED]> wrote on 11/04/2004 02:02:09 PM: > Wow even the 1st query seems to be taking a while...been like 5 minutes. > The DB has "only" around 80,000 records. I do have an index key which is > "email_address_id" so would adding that in the query help speed it up? > > Thanks for the quick reply & suggestion... > > > [EMAIL PROTECTED] wrote: > > > > > This query will list all of the email addresses where SOURCE='Source1' > > but are not listed within any other source > > > > SELECT et1.email_address > > FROM email_table et1 > > LEFT JOIN email_table et2 > > on et2.email_address = et1.email_address > > and et2.SOURCE <> 'Source1' > > WHERE et1.SOURCE = 'Source1' > > and et2.email_address is null > > > > There is a more generic version of this but it will respond much > > slower as you will create a much larger working table during the > > query's JOIN phase: > > > > SELECT et1.email_address > > FROM email_table et1 > > LEFT JOIN email_table et2 > > on et2.email_address = et1.email_address > > and et2.SOURCE <> et1.SOURCE > > WHERE et1.SOURCE = 'Source1' > > and et2.email_address is null > > > > Either query will respond much faster if there is an index (key) that > > has the SOURCE column listed first in its definition. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > Rob Kudyba <[EMAIL PROTECTED]> wrote on 11/04/2004 12:47:44 PM: > > > > > The email addresses from source1 that are also not part of all of the > > > following sources (source2, source4, and source6). > > > > > > What I meant was there are addresses that are duplicate and exist in > > > source 1, source 2, source 3, et. al. I want to only include the > > > distincts from all of them, in other words, ignore the address if it > > > also appears in either of the sources other than 1... > > > So: > > > source 1 has [EMAIL PROTECTED] > > > source 2 has [EMAIL PROTECTED] > > > > > > do no include [EMAIL PROTECTED] in the results of the SELECT... > > > > > > [EMAIL PROTECTED] wrote: > > > > > > > > > > > So what are you really after: > > > > > > > > All email addresses that are unique (not multiple-sourced)? > > > > The email addresses from source1 that are duplicated from no other > > > > sources? > > > > The email addresses from source1 that are not from any of the > > > > following sources (source2, source4, or source6)? > > > > The email addresses from source1 that are also not part of all of the > > > > following sources (source2, source4, and source6)? > > > > > > > > Often questions like this are posted because someone is trying to > > > > solve another problem and this is just an intermediate step. If > > that's > > > > your situation, there may be better ways of achieving your final > > > > result than going this route. What is it you eventually want to > > > > achieve with this list of email addresses? > > > > > > > > Shawn Green > > > > Database Administrator > > > > Unimin Corporation - Spruce Pine > > > > > > > > Rob Kudyba <[EMAIL PROTECTED]> wrote on 11/04/2004 12:26:42 PM: > > > > > > > > > I am trying to construct a SELECT statement on distinct email > > > > addresses. > > > > > Our DB has emails entered by various sources and there are duplicate > > > > > addresses based on the source. Using sample table names and sources > > > > here > > > > > is what I have: > > > > > > > > > > SELECT DISTINCT email_address > > > > > FROM 'email_table' > > > > > WHERE SOURCE = 'source 1' > > > > > > > > > > Simple enough, but I want to exclude email addresses that appear in > > > > > several other sources, i.e, something like in this pseudo sample: > > > > > > > > > > SELECT DISTINCT email_address > > > > > FROM [ENTIRE_TABLE_IN_DATABASE] > > > > > WHERE SOURCE = 'source 1' > > > > > AND EXCLUDE email_address IF SOURCE = [source 2, source 4, source 6] > > > > > > > > > > Thanks... > > > > > > > > > > -- > > > > > MySQL General Mailing List > > > > > For list archives: http://lists.mysql.com/mysql > > > > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > >
