Query Problem (rows too big)

[aas1611]

I have 2 tables called siemens1 and kompld1. They have the same fields, but siemens only has about 6500 rows while kompld has 273000 rows.

What I want to do is, to retrieve rows in siemens1 that also exist in kompld1. With the following query:

select
siemens1.f000,
siemens1.f008,
siemens1.f009,
siemens1.f094,
siemens1.f001,
siemens1.f002,
siemens1.f003,
siemens1.f004,
siemens1.f005
from siemens1
inner join kompld1 on siemens1.f008 = kompld1.f008 and siemens1.f009 = kompld1.f009

I received this error message:
ERROR 1104: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

Under MySQL options, I have already set Maximum Join Size = 10000000 (10 millions) and limit SELECT queries to Unlimited.

I thought it was just a simple comparison query.

Does anybody have idea? Thanks

 

[TonyGroves]

That message is appearing because the server calculates that the query could possibly return up to 1774 million rows. To suppress that, you can precede the query with:[tt]
SET SQL_BIG_SELECTS=1;[/tt]
If you want to permanently suppress that type of warning, you can put a line into the [mysqld] section of your my.cnf file:[tt]
sql_big_selects=1[/tt]

However, a better approach might be, if the field combination (f008,f009) uniquely identifies a record, to declare a unique index on those two fields in both tables. Then, the server should calculate that there can be no more than 6500 rows returned. (At least that's the theory; I haven't tested it).