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 -
mysql> explain select * from T1, T2 where AAA=BBB AND ZZZ like '%R%'; +------------------------+------+---------------+------+---------+------+--- ---+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +------------------------+------+---------------+------+---------+------+--- ---+-------------+ | T1 | ALL | NULL | NULL | NULL | NULL | 400 | | | T2 | ALL | NULL | NULL | NULL | NULL | 6591 | Using where | +------------------------+------+---------------+------+---------+------+--- ---+-------------+ 2 rows in set (0.08 sec) mysql> explain select * from T1, T2 where ZZZ like '%X%' AND AAA=BBB; +------------------------+------+---------------+------+---------+------+--- ---+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +------------------------+------+---------------+------+---------+------+--- ---+-------------+ | T1 | ALL | NULL | NULL | NULL | NULL | 400 | | | T2 | ALL | NULL | NULL | NULL | NULL | 6591 | Using where | +------------------------+------+---------------+------+---------+------+--- ---+-------------+ 2 rows in set (0.00 sec) Any inputs??? TIA, - Manish ----- Original Message ----- Sent: Saturday, December 04, 2004 10:26 AM Subject: Re: Help me optimize this query > If you change the update to a select you can use explain to see it's > execution path just like you would with a normal select. The rows > returned are the rows that would be matched by the update query. > Example: > > explain select * from t1, t2 where t1.YYY=t2.ZZZZ and t2.AAAA like '%X%'; > > Optimize the query then turn it back into an update. > > -Eric > > > On Wed, 1 Dec 2004 11:38:29 +0530, Manish <[EMAIL PROTECTED]> wrote: > > I am trying to execute this query and it is failing with Table is full error > > (I know I can make temp tables big). > > update t1, t2 > > set t1.XXX=1 > > where t1.YYY=t2.ZZZZ and t2.AAAA like '%X%'; > > > > My t1 has 10,00,000+ records and t2 has about 70,000 recorsds. I would like > > to know how can I optimize this query? > > What are the parmeters for this optimization? Can someone give me links > > where I can read up about such optimizations for update query. > > > > TIA, > > - Manish > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- > Eric Bergen > [EMAIL PROTECTED] > bleated.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
