Suggest the best query optimizati​on techniques in MySql database

[N.P. Reddy]

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 L Gibbons]

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]

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]

Hi,

yes..1 lakh=100,000

[N.P. Reddy]

It is taking <= 2 minutes time every fime to fetch the output

[Wendy L Gibbons]

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]

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.