This week's book giveaway is in the Performance forum.
We're giving away four copies of The Java Performance Companion and have Charlie Hunt, Monica Beckwith, Poonam Parhar, & Bengt Rutisson on-line!
See this thread for details.
Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help me optimize this query

 
Manish Hatwalne
Ranch Hand
Posts: 2595
Android Firefox Browser Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Loren Rosen
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you optimized the corresponding select query?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34681
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Manish Hatwalne
Ranch Hand
Posts: 2595
Android Firefox Browser Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Loren Rosen
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic