• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Suggest the best query optimizati‚Äčon techniques in MySql database

 
N.P. Reddy
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1110
Eclipse IDE Oracle VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1110
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

yes..1 lakh=100,000
 
N.P. Reddy
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is taking <= 2 minutes time every fime to fetch the output
 
Wendy Gibbons
Bartender
Posts: 1110
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2747
38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic