A rule of thumb I always follow is to design the DB so that you don't have to add columns (or modify the structure) unless something unforseen comes up (like a new feature). In your case, you are expecting to add "variables" in the future, so I would deign accordingly. Use rows instead of columns. Using rows also allows you to index all variable values and names, since there are only two columns. You couldn't index all your columns if you expanded horizontally instead of vertically. Besides, it's a heck of a lot easier to add a row than a column.
On Thursday, August 1, 2002, at 01:09 AM, Michael Zelina wrote: > Can someone provide insight into what a good maximum number of > columns is in a MySQL database? I have an extensive list of variables > for an engineering application which number in the 700+ range with > more additions possible down the road. My question is: should I break > up the variables into several tables for storage, or can I use a > single table with this many columns. I cannot find any good rules of > thumb for how much the number of columns effects performance. > > In trying to consolidate my existing data from Access (which limits > columns to 255 > per table) into a single table, I received the following error: > > mysql> create table tblsessions select * from tblsessionfiles, > tblsessionfiles2, > tblsessionfiles3 WHERE tblsessionfiles2.sessionid_2 = > tblsessionfiles.id38 > AND tblsessionfiles3.sessionid_3 = tblsessionfiles.id38; > ERROR 1118: Too big row size. The maximum row size, not counting BLOBs, > is 65535. You have to change some fields to BLOB > > I assumed that the maximum row size is the total bytes for the entire > row. My > questions are: (1) can I change this without recompling MySQL and (2) > should > I really do this or just stick with three or more separate tables for > performance > reasons. > > FYI, I am running this on a well-equipped server and will probably only > have a > maximum # of concurrent users doing DELETE and INSERT in the range of > 150 or so. > > Thanks, > Mike > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <mysql-unsubscribe- > [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
