> Hi there
> I'm developing a large web application. Part of this web application will
> be storing numerical chart data in a MySQL table - these numbers will be
> already calculated, and are just being stored for reference.
> In this particular table, the stored data will never be deleted or
> changed. The only actions performed will be SELECTs and INSERTs. There
> will never be any DELETEs or UPDATEs.
> The end-result charts themselves have many rows. Let's say about 40-100
> rows per chart. I will be storing thousands of charts. I am definitely
> looking at the possibility of there being millions (upon millions?) of
> rows at some time in the future. I am very aware of relational database
> concepts, and one large table truly makes the most sense in this
> particular situation. Believe me, there are foreign keys up the wahoo.
> A general question to the experts out there (or just people with
> experience ;))... what kind of things should I 'look out for', or
> prepare for, when designing/using a table that is so large? My previous
> MySQL tables in all my years of programming have never really had more
> than 40,000 rows. This table will of course be the largest table in a
> database that holds many more tables (about 20 or 30).
> Here are things I am concerned/wondering about. Any advice on them would
> be very much appreciated.
> 1) Table type: all the tables in the database that depend on data being
> inserted/updated to other tables are all InnoDB using transactions. This
> table, as mentioned, will strictly be INSERTed to and SELECTed from.
> Should I use MyISAM or InnoDB? Just about all columns will be storing
> numbers, possibly a varchar column or two, and no blobs/text columns.
> However, these columns will need to be 'searched' sometimes.
> 2) Backing up / Restoring: What should I look out for in terms of
> dumping/restoring the table? Anything other than the traditional
> 3) If I do go with an InnoDB table... I imagine I will probably have to
> change that 'ibdata' setting, or at least get familiar/involved with it,
> since this table will be so large. I've seen the term 'multiple
> tablespaces' in reference to InnoDB tables. Is this something I should
> be investigating for a table of this size? What exactly does it mean?
> 4) Do I need to think about tion of any sort? Or slave/master
> things? Or can I just use the database as is? There would never be more
> than 50 or so people using the database at once (if that matters).
> The system is a P4 (2.8), running Linux RedHat, MySQL 4.1.7 (though I'm
> pretty sure I will have it upgraded to MySQL 5 before putting this into
> production or even building it - or do I even need to), 1 GB of RAM....
> basically our own box being managed/hosted somewhere across the
> I know this is a lot of advice to ask. I'm self-taught, and have been
> re-reading some MySQL books in preperation for this project, which will
> be my 'largest' to date, and will be quite complex overall (this is a
> small but still critical part of the overall project). The books teach
> well, but they don't offer to much advice to specific situations ;)