CODEWITHSUNDEEP
mysqldatabasedatabase-design
darkcloudbird
darkcloudbird

Reputation: 23

Design tips for large database tables?

A Database newbie here.

I would like to plan a sales invoice database in which millions of records/rows will be added a month.
The data inside is not that heavy but a few information like id,amount, etc...
The problem is that table needs to be updated very (very) frequently.
I am concerned that table will grow so quickly and slow down the system in the future.
I am looking for a general design tips how should I update that kind of table.
I have some other tables in my schema but that table is the most important one and the most frequently updated.
Is there any design/tip/architecture so I do not have to update that table directly.

Here is my platform in brief.

Application : java/spring mvc
Database    : mysql
OS          : CentOS 6

Upvotes: 1

Views: 1165

Answers (1)

Johan
Johan

Reputation: 76567

Rule 1: don't worry about performance before slowness hits you.

Rule 2: normalize those tables up to at least 3NF

Rule 3: If you have lots of updates, go easy on the indexes and do not declare unique indexes. 

do something like:

id integer not null primary key auto_increment  <<-- simple primary key
amount decimal(10,2) not null
.....

Rule 4: The size of a table does not affect the insert speed. (Unless you have unique indexes)

Tip: Use InnoDB.

Upvotes: 1

Related Questions