SELECT QUERY too BIG when inserting records 1

[aas1611]

Hi!

The following is always a problem to me. I've asked about this before, but so far have no luck.

MySQL Version is 4.1.13.
I have two tables, let's say A and B. Table A has 150K records, Table B has 300K.
Both have unique index on field F1, F2, F3 (these fields are in both tables).

What I want to do is:
I want to insert records from Table B into Table A, only the records that are not yet in Table A
(so it could be all records in Table B).

I wrote:

insert into A
select B.*,
'','','' #Table A has more fields than Table B
from B, A
where B.F1 != A.F1 and B.F2 != A.F2 and B.F3 != A.F3

The error message was that the SELECT query is too big and I have to check the WHERE clause.
MAX_JOIN_SIZE on my database is already 1000000 and I want to avoid SET SQL_BIG_SELECTS=1.

Does anyone know a better WHERE clause than mine? or should I do SET SQL_BIG_SELECTS=1?

Any hint will help! THANKS!

Andre

 

[TonyGroves]

Your query's error message occurs because you have a result set of up to 150k x 300k = 45,000,000,000 records.

You could instead use:
[tt]
INSERT IGNORE a SELECT *,'','','' FROM b
[/tt]

 

[aas1611]

This IGNORE command, is it going to insert all records in table B into table A, no matter what's in table A (so table A will have 150+300=450K records), or see first if records in B already in A?

 

[TonyGroves]

IGNORE will cause any duplicate records to be discarded. It's explained in the MySQL manual, here:

http://dev.mysql.com/doc/mysql/en/insert-select.html

.

 

[aas1611]

Yeah!
I've tried it and it seems that it works. Thanks for your help!

 

[aas1611]

I have one more question, though.

When I use this IGNORE command, my table A is getting very big. This is because, in table A and B there are date and time column. Of course date and time are always different from time to time.

To be more clear, here is the field:

Table A:
date time name address
------- ------ ----- ---------
2005-10-03 01:00:00 John Flower St.9

Table B:
date time name address
------- ------ ----- ---------
2005-10-04 02:00:00 John Flower St.9

In this case, record in table B is NOT supposed to be inserted into A because fields name and address don't change. But with the IGNORE mentioned above, this record is inserted because field date and time are different (not duplicate).

Any more hint? Thanks!

Andre

 

[TonyGroves]

How are your records (in the destination table) uniquely identified? If they are identified by a unique name and address, then you would need to declare a unique index on those fields, and try again.

 

[aas1611]

Before I go further,
we are talking about different tables that I have described at the beginning of this thread (that one is solved, thanks). The situation is now a little bit different, even though, I think the logic is kind of the same.

Now the table is used for history. It can't have unique index. It contains all records that have changed, so it has to have duplicate data (once again, only if something has changed, like name, address, etc). Fields that don't change (cant' be changed) are his/her bank code and account number. This history table is updated everyday. When I use:

INSERT IGNORE history
SELECT * FROM T1 -- T1 is a temp table

this command will insert all records even though nothing changes. It is because of the date and time fields (see my 'John - Flower St' example).


I don't know if it makes sense to you.

My temporary solution is that another temporary table (T2) is created with
SELECT
min(date_field),
min(time_field),
...
FROM history
GROUP BY all other fields

Then I delete the history table, and insert records from T2 back into the history table.

Well?