• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Liutauras Vilda
  • Henry Wong
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Mikalai Zaikin
  • Himai Minh

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

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 L Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

yes..1 lakh=100,000
 
N.P. Reddy
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It is taking <= 2 minutes time every fime to fetch the output
 
Wendy L Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Saloon Keeper
Posts: 7377
170
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic