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


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
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: 2578

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29237
    
139

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: 2578

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
 
Similar Threads
Deadlocks in Threads?
Query to not duplicate join results (resolved)
need help in pivot
Wait and Sleep methods
Updating multiple tables in one sql query