I am in the middle of designing a new software package and wondered if any of you have had experience with speed and numbers of records in a mysql database design.
My current problem is this.
I could design a "history" table that would be populated with about 2000 records per day (750,000 record per year!)
OR
I could design (approx) 10 tables that would be populated with the same amount of records (but more space would be taken with key fields and indexes).
So would it be better for 1 large file so:
1/ I can easily select all the records I need without any major sql statements (and make it easier for the user to do this too)
2/ Less data space would be taken
3/ Easier to code
4/ UNKNOWN retrieval speed
5/ Easier to manipulate 1 file (for deletes, backups etc)
OR
Would it be better for 10 files so:
1/ It would be more complicated file joins (for the user aswell!)
2/ More data space would be taken
3/ Harder (?) to code
4/ Probably (?) faster retreival
5/ Harder (?) to manupulate several files (for deletes etc)
Do any of you have any thoughts on this? (MySQL says that it can easily process millions of records.)
Have any of you done something similar.
All thoughts and points of view are very welcome (whatever street you live in 😉 )
Thanx
Pryce
