Help me optimize this query

I am trying to execute this query (MySQL DB) 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

Have you optimized the corresponding select query?

Manish,
Do you have an index on t2.AAAA ?

Also some databases care about the order of the where clause. Try reversing it and seeing if there is an improvement.

Originally posted by Manish Hatwalne:
where t1.YYY=t2.ZZZZ and t2.AAAA like '%X%';

I think it is better then reverse. because the second condition may result in more results. the only thing i can suggest is the same suggested by Jeanne. make index on t2.AAAA.

Tip: do not use like operator in your query especially when huge no of records. unless you really need it.

thanks.

I need this "like" operator, there's no other way.
Unfortunately, I can't afford to experiment to much now, is here a way I can logically predict its time and more importantly space utilization? Approximation will do, but I need to know which one will use more resources here.

I need to look for indexing on t2.AAAA.

Is there a place (links/books) where I can go and read about all this? How those query results are stored in temp tables and all that "under the hood" details.

TIA,
- Manish

As a start, you should run EXPLAIN on the query. Show the result here if you don't know how to interpet it.

Then there's a separate question about how to learn about optimizing queries, both generally and specifically to mysql. There are various books out there; perhaps someone who's read the more recently published books has some reccomendations.