File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Suggest the best query optimizati​on techniques in MySql database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Suggest the best query optimizati​on techniques in MySql database" Watch "Suggest the best query optimizati​on techniques in MySql database" New topic
Author

Suggest the best query optimizati​on techniques in MySql database

N.P. Reddy
Greenhorn

Joined: Apr 11, 2012
Posts: 3
Hello All,

In my java project using MySql database (JDBC interface).In that database one table contains around 32 lakhs of records and that table is joining with other tables. While retrieving (fetching) data performance is degrading (to fire query taking <=2 minutes every time).So need to improve the performance time while retrieving.

Cheers,
N.P.Reddy
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

J.P. Welcome to the ranch

and for people like me (who just had tolook it up) a lakh is a unit in the South Asian numbering system equal to one hundred thousand (100,000)
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

is that 2 seconds for every query, even one using a unique id that is indexed?

or is that for a big query returning all rows?

could you provide an example of a select that is taking to long, with a simple table description including indexes (if a table contains 100 columns only tell us the keys and foreign keys and columns in the joins)
N.P. Reddy
Greenhorn

Joined: Apr 11, 2012
Posts: 3
Hi,

yes..1 lakh=100,000
N.P. Reddy
Greenhorn

Joined: Apr 11, 2012
Posts: 3
It is taking <= 2 minutes time every fime to fetch the output
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

N>P you an a private message about an administration issue.

Every single select from the simplest to the most complicated is taking as long, that suggests to me the problem isn't in the database but in something like the network.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
We can't really advise without knowing further details. As Wendy said, retrieving a single record with a unique indexed key should take a fraction of a second. As should most queries in a transactional DB, actually. For an analytical DB (like one used for OLAP) 2 minutes might be OK for certain queries. So, it depends. Tell us more.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Suggest the best query optimizati​on techniques in MySql database
 
Similar Threads
MySQL and Servlets
Hibernate optimization problem
select * vs select column1,column2 ..
records not showing in ResultSet
Passed 1z1-043