Although I've removed myself from the list some time ago I'm hoping for
your advice in this matter. If you post to the list, please CC me at the
address in the footer as well if you feel it's important. Thanks!
I'm about to start designing a rather big project, atleast it's the
biggest one I've been working on. I can not estimate the number of
records or the average size of a record. If things go well it will be
used by about 1000 users and of those around 200 at the same time. The
number of unique columns in the database will probably be around 500,
not counting indexes. We're using all kinds of variable types; chars,
floats, ints, BLOBs and so on.
My question is, is it more efficient to have all these columns in one
big database with several tables or a number of smaller databases with
several tables? What I'm looking for is pure speed improvements,
whichever way MySQL prefers.
The columns are used for completely different things. Some are
associated with service, some with manufacturing, some with
administration, some are purely internal for the system and so on so I
feel that the approach with one big database limits the possibilities to
get a proper structure since it effectivly removes one layer of
identification. I might be very unclear here since I don't know how to
put this so let me present an example. If I choose one database I get
this layout: DB-TBL-COL. I can then only use one unique name per
'level', a table have to be named uniquely in this setup. If I go like
this: DB1-TBL-COL, DB2-TBL-COL, and so on I can give any number of
tables the same name as long as they do not exist in the same DB. If,
however, MySQL runs significantly faster with all the tables in one big
DB, that's the way we'll do it.
Hardware is not an issue since we'll be getting whatever disk/ram/cpu
necessary to run this. I will be using MySQL (of course) under Linux
with PHP3 as the scripting language.
Any and all ideas are more than welcome since I'd rather not redo all
the databases 3 months into development. 8)
Spirex Digital Design
Phone: +46-31-514651, 0708-113711
S-422 43 Hisings Backa