List:General Discussion« Previous MessageNext Message »
From:Goutham S Mohan Date:December 16 2004 8:05am
Subject:Re: Help me optimize this query
View as plain text  
>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 
Thread
Help me optimize this queryManish1 Dec
  • Re: Help me optimize this queryEric Bergen4 Dec
  • Re: Help me optimize this queryManish8 Dec
Re: Help me optimize this queryGoutham S Mohan16 Dec