Mysql 5.0.90 around 500 columns question

[dsp77]

Hello,

I'm working with an application that needs to create around 500 columns in a table. My question is what will happen when some dozen people will select/insert into the table, does it affects performance (the server has big amount of resources 6core 5gigs ram and the mysql version is 5.0.90), does it crash. I know that the limit is 4096 according to mysql documentation.

I know this isn't a practical solution but there is another app (that i cannot modify) witch requires to read the table with all columns.

Thank you

[Adam]

Inserting into the table alone, whilst still obviously taking longer than a thinner table, shouldn't be much of an issue. Do you have any indexes on the table however?

[dsp77]

i'm not using indexes i have just one id primary auto increment and the rest its just data

[PFMaBiSmAd]

Are you just selecting/inserting rows with this many columns or are you actually "create(ing) around 500 columns in a table"?

[dsp77]

to be more clear ill create the table with 500 empty cols

id=>primary auto increment INT(11), data1 ......... data500=>varchar(255).

The query's will be like:

insert into table data1....data500

update based on id

and select based on id

that's all no joins or other complex query's

[Adam]

I doubt you'll suffer much with performance then. When you say 'some dozen' - is that all at once, repetitively?

[ManiacDan]

Why are you even doing it like this?  There's usually no reason to store repeating data in large numbers of columns like this.  That's why databases have ROWS.

[dsp77]

@MrAdam there will be around 100-150 accounts per day

@ManiacDan i need them in this way because another program connects to the mysql and it needs the data to be stored in a single table for analysis.

[kickstart]

Hi

 

One possibility would be to design several tables to store the data more efficiently and then create a view joining those tables to get the 500 columns required by the existing program.

 

All the best

 

Keith