>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 - >- Manish
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. Regards, Goutham S Mohan --------------- Software Engineer, Hewlett Packard [GDIC] __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
