Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Million records in MySQL ?

 
Ganesh Pat
Ranch Hand
Posts: 79
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 2756
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 79
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 2756
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 79
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Once again thank you Jayesh Lalwani !
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic