big table

[Ward Ricker]

I have created a very large table with over 2500 fields in it, and I get an error message when I try to create the table saying that I have too many columns. I have come up with a logical way to break the table into four tables, but one of those tables (with approx. 1000 fields) still won’t create. I am not seeing any logical way to break up this table, since my script runs a loop which loops through all those fields. The limit for fields in a MySQL table is supposed to be 4096, so I created a test script to create a table with as many fields as I can squeeze into it. bit(1) is the smallest possible size for a field (right?), so I made all my fields bit(1), but the script will only create a table with (exactly!) 1000 fields. It fails if I try to create 1001 bit(1) fields in a table. (The actual table in my program is mostly bit(1) fields, but also has some bit(3) fields.) If 4096 is the limit, shouldn’t I be able to get 4096 fields if I am using the smallest field size possible? If I can’t increase the number of fields to the number I need, is there any way to work around this problem, given that I need to run a loop using all the data?