Re: newbie join issue

[Edward Ritter]

Thanks, adding the indexes worked beautifully. I'll go knock my head on 
the desk now. Thanks for your time :)

Ed

Edward Ritter said the following on 7/20/2004 1:08 PM:

> Thanks, I'll take a look at that. The id isn't unique, so that's why I 
> added the idx column.
> 
> Does my query look okay beyond that? I'll add the additional indexes and 
> try again.
> 
> Ed
> 
> Garth Webb said the following on 7/20/2004 1:03 PM:
> 
>> What is the 'idx' for when you already have an 'id' column?  Also, you
>> need an index on the column that you are joining on;  having a single
>> indexed column on a table doesn't automatically improve all queries
>> against that table.  Put an index on the 'email_address' fields of both
>> tables.  You'll need:
>>
>>     ALTER TABLE la_entire
>>     ADD INDEX idx_email_address (email_address);
>>
>>     ALTER TABLE la_final
>>     ADD INDEX idx_email_address (email_address);
>>
>> See:
>> http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
>>
>> On Tue, 2004-07-20 at 09:22, Edward Ritter wrote:
>>
>>> Stefan:
>>>   I added an index column to each after I imported. Here's a listing 
>>> of the two tables.
>>>
>>> la_entire
>>> +----------------+-------------+------+-----+---------+----------------+
>>> | Field          | Type        | Null | Key | Default | Extra          |
>>> +----------------+-------------+------+-----+---------+----------------+
>>> | id1            | int(3)      |      |     | 0       |                |
>>> | id2            | varchar(6)  |      |     |         |                |
>>> | first_name     | varchar(30) |      |     |         |                |
>>> | last_name      | varchar(30) |      |     |         |                |
>>> | street_address | varchar(50) |      |     |         |                |
>>> | city           | varchar(30) |      |     |         |                |
>>> | state          | char(2)     |      |     |         |                |
>>> | zip            | varchar(9)  |      |     |         |                |
>>> | email_address  | varchar(50) |      |     |         |                |
>>> | idx            | int(7)      |      | PRI | NULL    | auto_increment |
>>> +----------------+-------------+------+-----+---------+----------------+
>>> 10 rows in set (0.00 sec)
>>>
>>> +----------------+-------------+------+-----+---------+----------------+
>>> | Field          | Type        | Null | Key | Default | Extra          |
>>> +----------------+-------------+------+-----+---------+----------------+
>>> | id             | int(5)      |      |     | 0       |                |
>>> | county         | int(5)      |      |     | 0       |                |
>>> | precinct       | int(5)      |      |     | 0       |                |
>>> | last_name      | varchar(30) |      |     |         |                |
>>> | first_name     | varchar(30) |      |     |         |                |
>>> | src_address    | varchar(30) |      |     |         |                |
>>> | src_city       | varchar(30) |      |     |         |                |
>>> | src_state      | varchar(20) |      |     |         |                |
>>> | src_zip        | int(5)      |      |     | 0       |                |
>>> | email_address  | varchar(30) |      |     |         |                |
>>> | new_city       | varchar(30) |      |     |         |                |
>>> | new_state      | varchar(20) |      |     |         |                |
>>> | new_zip        | int(5)      |      |     | 0       |                |
>>> | new_zip4       | int(4)      |      |     | 0       |                |
>>> | new_address    | varchar(30) |      |     |         |                |
>>> | dma_flag       | varchar(4)  |      |     |         |                |
>>> | deceased       | varchar(4)  |      |     |         |                |
>>> | phone          | int(12)     |      |     | 0       |                |
>>> | time_zone      | varchar(4)  |      |     |         |                |
>>> | phone_sol      | varchar(4)  |      |     |         |                |
>>> | cluster        | varchar(4)  |      |     |         |                |
>>> | age            | varchar(4)  |      |     |         |                |
>>> | income         | varchar(4)  |      |     |         |                |
>>> | pres_child     | varchar(4)  |      |     |         |                |
>>> | own_rent       | varchar(4)  |      |     |         |                |
>>> | length_of_res  | varchar(4)  |      |     |         |                |
>>> | buyer          | varchar(4)  |      |     |         |                |
>>> | responder      | varchar(4)  |      |     |         |                |
>>> | gender         | varchar(4)  |      |     |         |                |
>>> | occupation     | varchar(4)  |      |     |         |                |
>>> | education      | varchar(4)  |      |     |         |                |
>>> | donor_prospect | varchar(4)  |      |     |         |                |
>>> | scr1ast1       | varchar(4)  |      |     |         |                |
>>> | scr1bst1       | varchar(4)  |      |     |         |                |
>>> | scr2ast1       | varchar(4)  |      |     |         |                |
>>> | scr2bst1       | varchar(4)  |      |     |         |                |
>>> | decile1        | varchar(4)  |      |     |         |                |
>>> | decile2        | varchar(4)  |      |     |         |                |
>>> | decile3        | varchar(4)  |      |     |         |                |
>>> | decile4        | varchar(4)  |      |     |         |                |
>>> | scr1ast2       | varchar(4)  |      |     |         |                |
>>> | scr1bst2       | varchar(4)  |      |     |         |                |
>>> | decile5        | varchar(4)  |      |     |         |                |
>>> | decile6        | varchar(4)  |      |     |         |                |
>>> | dob            | varchar(12) |      |     |         |                |
>>> | party          | varchar(4)  |      |     |         |                |
>>> | idx            | int(7)      |      | PRI | NULL    | auto_increment |
>>> +----------------+-------------+------+-----+---------+----------------+
>>> 47 rows in set (0.00 sec)
>>>
>>>
>>> My latest attempt at a query is this:
>>>
>>> select la_entire.* from la_entire left join la_final on 
>>> la_entire.email_address = la_final.email_address where 
>>> la_final.email_address is null;
>>>
>>> Any help?
>>>
>>> Ed
>>>
>>> Stefan Kuhn said the following on 7/20/2004 12:05 PM:
>>>
>>>> I would expect that the speed problems are due to missing indices. 
>>>> Did you do proper indexing? If unsure, post your table structures 
>>>> and query.
>>>> Stefan
>>>>
>>>> Am Tuesday 20 July 2004 17:45 schrieb Edward Ritter:
>>>>
>>>>
>>>>> I've got a task that's gonna require me to compare one table to 
>>>>> another
>>>>> and remove the rows from the first table that are found in the
> second
>>>>> table that match email_address.
>>>>>
>>>>> I'm running 4.0.20a-nt-log. The first table has 10 colomns and 
>>>>> about 50K
>>>>> records, and the second table has 46 columns and has about 16K 
>>>>> records.
>>>>>
>>>>> I've attempted a number of selects that just sat and hung the 
>>>>> computer.
>>>>> I know I must be doing something wrong. I figure I'll need to do a 
>>>>> left
>>>>> join on it, but I've not had much experience with joins as such and
> I
>>>>> need a little assistance.
>>>>>
>>>>> Can anyone help me work this out? If you need more info, let me
> know.
>>>>>
>>>>> Ed
>>>>
>>>>
>>>>
>>
>