List:General Discussion« Previous MessageNext Message »
From:Manish Date:December 8 2004 5:32am
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 -


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 <prolist@stripped> 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/mysql?unsub=1
> >
> >
>
>
> --
> Eric Bergen
> eric.bergen@stripped
> bleated.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