>Thanks for your inputs Eric.
>I tried this but it couldn't give me any insight abt
>how can optimize this
>for space saving of temp tables.
>Maybe I am not expert enough to interpret this
output, >so here it is -
Seeing the explain log, it looks to me like you donot
have any index defined on t1 / t2. Having indexes on
columns you frequently query on should help reduce
space and time. In your case t1.YYY or t2.ZZZ
Is there a foreign key relation ship between t1.YYY
and t2.ZZZ ?
Also, is it not possible to use nested queries in the
mysql version you are using. I am not sure from what
version nested queries are supported if at all :-
select * from t1 where t1.YYY in (select t2.ZZZ from
t2 where t2.AAA like '%bla%');
If the nested queries are not supported then you would
have to use temporary tables or heap tables in mysql
to generate a temporary result by breaking the query.
insert into "temporary_table" select t2.ZZZ from t2
where t2.AAA like '%bla%';
Then use this temporary to join with t1 in an update.
Again, before trying this, make sure you have all
indexes, foreign keys defined. Defining this itself
may solve your problem.
Goutham S Mohan
Hewlett Packard [GDIC]
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around