Thanks for all the input. The problem i have with this idea: > Really? Wow, my opinion is that you're trying to do in one table what > you should do in two. Have a questions table, and an answers table. The > answers table would have a column specifying which question they belong > to. i.e. > > QUESTIONS > int autoincrement questionID > question > > ANSWERS > int autoincrement answerID > int questionID > answer
is, that 1) i must define the column with the value of the answer as a TEXT to cover all possible answers, altough a SMALLINT might fit it better. but i can live with that 2) i think it's slow when i have lots of data. i'm currently not at home to do performance tests, but let's assume i have 2000 questions, then i collect by user input 2000 answers to every question by 10000 different users (a realistic scenario for our application) and store it in a table like this: ANSWERS int autoincrement answerID int userID int questionID text answer i then have 2000*10000 = 20 mill. rows in ANSWERS. now let's assume i want to select all answers to all questions for 100 random users at once. The only solution i see here for one query is a large join which will take really, really long to get the data even with proper indexes or did i do sth. wrong? is there a way to provide a view on these two tables (QUESTIONS and ANSWERS) that simulate the original table i intended, which is actually fast even with large amounts of data? Are other dbs like oracle better on doing joins on such large tables? So i really like the idea of having one large table with many columns because it's really fast and i can define each column properly but i currently see no option to go this way but with that way there will alway be a limit to the max. amount of answers in the system. regards, Fabian On Mon, 16 Apr 2007 23:39:40 -0700, "Micah Stevens" <[EMAIL PROTECTED]> said: > Fabian Köhler wrote: > > Hello, > > > > i have table with answers to questions. Every answer is a column in the > > table. i.e. > > > > id|q1|q2|q3 > > 1|answer1|answer2|answer5 > > 2|answer3|answer4|asnwer6 > > > > another option to save it would be sth like this: > > > > id|field|value > > 1|q1|answer1 > > 1|q2|answer2 > > 1|q3|answer5 > > 2|q1|answer3 > > ... > > > > The last one is not really useable when working with large amounts of data, > > when you want to select i.e. 200 questions with answers it's 200*nof > > answers queries to get them. > > > > > Really? Wow, my opinion is that you're trying to do in one table what > you should do in two. Have a questions table, and an answers table. The > answers table would have a column specifying which question they belong > to. i.e. > > QUESTIONS > int autoincrement questionID > question > > ANSWERS > int autoincrement answerID > int questionID > answer > > Then if you want all the answers to a question, you just grab them: > > select * from answers where questionID = '100' > > Or, you can do a join, and get the question information in the same > query. > > -Micah > > The problem with the first solution is, that MyISAM storage engine is > > limited to 2599 columns i think. So what's happening if i have more answers > > than columns available? > > > > Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an > > option, they are all to slow. What's the "right" way to store and select > > such information? > > > > Thank you very much. > > > > regards, > > > > Fabian > > > > -- > > Fabian Köhler http://www.fabiankoehler.de > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
