100+ Columns in a table. Good or Bad?

[ajoo]

Hi all ! 

 

I was told by someone today that it's a bad idea to have more than 7 columns in a table in a MySql database ???! Is that true?

 

I am using tables which have about a 130 columns and I was told that that was a bad idea and would cause the application to crash once the number of simultaneous users exceed a certain number , namely about a 100. 

 

I request the Guru's to kindly clarify on this.

 

Thanks all !

[gizmola]

Is it bad? Yes, it indicates that the structure is most probably lacking an understanding of relational design, but that is a large topic I'm not going to try and introduce.

 

The limits on table design are described here: http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

 

The practical limit is that a mysql row can not be larger than roughly 64k in size.

 

Will it cause the system to crash at some arbitrary point?

 

That's really hyperbole. It depends on the nature of queries, how much data, how much memory is available and a lot of other factors. Also, "crash" is imprecise. Web Servers don't typically "crash". What can happen is the server can become inaccessible for a time, and the individual php scripts can run out of memory or timeout.

 

As for '7' being a magic number of columns -- no, there's nothing like that. There are limits to the number of indexes you have, and if you have a giant table, that might be because you have many repeating groups in there, that should be broken out into individual tables. You can identify these easily if you have columns like thing1, thing2, thing3 etc.

 

In short, to have any meaningful discussion about database design, we'd need to know a lot more specifics about the application, the table design, typical queries, number of simultaneous users, etc.

[ajoo]

Hi gizmola ! Thanks for the reply. 

 

Well here are a few more details. 

 

1. It is a big table indeed, but I don;t think it's lacking DB design since only related data is scored in it. Most of this data is scores (floats)-about 90, a few ( 3 ) dates, a session ID field and a few ( 3 more) logical (int) fields. This is not the only table. Other tables score the personal details and details related to the games being played etc. There is no repetition of data and relational DB rules are being followed. The Table is of the type InnoDB

 

2. During each session,  the last row is accessed from the table and a new row (using INSERT and UPDATE ) is created during the game.  The scores are read to display the data in a tabular form. So each user in his or her own session can read from the table using a specific ID which insures that there is no conflict in reading the rows by users. Same for updation. When a user logs into a new session and there is no record (row) for the new day, it is requested on first Insert of the data.(1st set of data is inserted and the inserted row ID obtained using  

$InsertedrowID = $stmt->insert_id;

Subsequent sets of columns for the day use this ID for inserting the rest of the columns (the remaining 80). Hopefully there are no conflicts. Simultaneous testing on 4 users works great. ( I know its too low compared to what would be in actual terms ). I think there would be no read or write conflicts.

 

3. Simultaneous users can run into thousands. 

4. I used the wrong word maybe crash. What I meant was what you said about the server being too occupied for some time. 

 

Would that be enough information to guide me a bit more on this. 

Thanks again for the information. Looking forward to some more !!

[Barand]

Not much I can add to Gizmola's reply and I don't know how your application is processing the table.

 

If you find a lot of the scores in each record are unused, or only one score is updated at any one, time then instead of

+--------+-----+-----+-----+--------+--------+-----+---------+
| userid |  X  |  Y  |  Z  | score1 | score2 | ... | score90 |
+--------+-----+-----+-----+--------+--------+-----+---------+

you could consider putting the used scores in a separate table, one score per row

                  USER TABLE
+--------+-----+-----+-----+
| userid |  X  |  Y  |  Z  |
+--------+-----+-----+-----+
    |
    |
    +----------------+
                     |              
                     |              SCORE TABLE
                +---------+---------+---------+
                | userid  | scoreid |  score  |
                +---------+---------+---------+

[ajoo]

Thank you Guru Barand for looking into this. 

 

 I have written in my reply how the table is processing information. Namely the information is updated in 9 groups of about 10 scores each. Scores are recorded every day. They are then retrieved to be displayed as a graph. So a user can just see his or her progress for the number of days elapsed since the current day.

 

What more information would you like me to provide about the processing of the tables data that could help you answer this in some more detail? I would be glad to provide.

 

Thanks very much.