• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Million records in MySQL ?

 
Ranch Hand
Posts: 79
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Once again thank you Jayesh Lalwani !
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic