I also thought about creating a "materialized view" with mysql by doing: create table vanswers (select ... query to get the table in the format..) or a stored procedure which generates a table like the one below, but all solutions seem to be slow like hell due to the high large joins which are necessary to create such tables.
desired table: id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 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]
