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.
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
Joined: Feb 12, 2003
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.