aspose file tools*
The moose likes JDBC and the fly likes Help me optimize this query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help me optimize this query" Watch "Help me optimize this query" New topic
Author

Help me optimize this query

Manish Hatwalne
Ranch Hand

Joined: Sep 22, 2001
Posts: 2581

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

Joined: Feb 12, 2003
Posts: 156
Have you optimized the corresponding select query?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31070
    
232

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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

Joined: Sep 22, 2001
Posts: 2581

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

Joined: Feb 12, 2003
Posts: 156
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help me optimize this query