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 Million records in MySQL ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Million records in MySQL ?" Watch "Million records in MySQL ?" New topic
Author

Million records in MySQL ?

Ganesh Pat
Ranch Hand

Joined: Feb 04, 2012
Posts: 79

hii friends. I've developed a desktop application where each day thousands of records will be inserted so many queries will be fired on database to show different kinds of reports. how to use MySQL for that ?.
My table for an example has fields like
emp_id(pk),name,birth_date,mobno,salary,balance,address.

in some table more than 8 columns are there.
Will it slow down record fetching speed as more rows get inserted in database ? I've never tried MySQL with such huge records in it so please need help.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2447
    
  28

MySQL can support millions of records, but you need to tune your queries, database schema and database settings. I've used MySQL couple of years ago, and the single most common reason for performance problems is when it starts using temp space and high IO. There are number of things that can cause it to use temp space and increased IO. Bad queries might load lot of records. Adding indexes reduces the need to processing of large records, and you can configure the database to use more memory. usually you fix most problems by using a combination of approaches.
Ganesh Pat
Ranch Hand

Joined: Feb 04, 2012
Posts: 79

Thank you Jayesh A Lalwani so you mean to say that I need to fire appropriate query which will fetch only required data ex. If i need only one column then not to use select * right ? , Need to use Indexing and Database settings what do you mean by database settings ?

Is it important to use foreign key in tables ? yes Primary keys of some tables are used in other tables but I'm not going to delete any records of database so is it necessary to use Foreign keys ?
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2447
    
  28

There are tradeoffs for almost any choice that you are going to make. If you are looking for a list of dos and donts, you are looking for the wrong thing. No one will be able to give you a handy list of things to do. What you do and don't do depends on the needs and design of your application.

If you are extremely new to MYSql, you will have to learn how to use explain plan to optimize queries. This is a good place to start. It tells you how to ue explain plan, and optimize your sql queries. Don't worry about optimizing the database settings for now. Mysql comes with inbuilt .cnf files based on the size of the database. Just use the one that matches your requirement closest
Ganesh Pat
Ranch Hand

Joined: Feb 04, 2012
Posts: 79

Once again thank you Jayesh Lalwani !
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Million records in MySQL ?