If the possible answers are not predetermined, you have a real problem. I don't know how you can optimize that. A full-text index might help, but I don't have a good understanding of the type of queries you're going to need.
I am not a good person to ask about performance, nor am I familiar with views (still running 4.1.23 here). Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Fabian Köhler [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 17, 2007 10:09 AM > To: 'Jerry Schwartz' > Cc: 'mysql@lists.mysql.com' > Subject: RE: Max columns in a tabel in MyISAM storage engine > > >> The "right" way to do this would be your second way, where > there would be > >> one row per answer. I can't really grasp how the questions > and answers > >> relate to anything else, so I'm making a lot of assumptions. > >> > >> If you are concerned that this technique is too slow, > because it involves > >> linking two tables, there is an ugly alternative. You > could store all of > >> your answers in a single text field, with some kind of > delimited format: > >> > >> "q1=answer1,q2=answer2,q5=answer5" > >> > >> And then parse it in your program. > > > >And eliminates the possibility to do any queries against > your database > >that make any sense whatsoever. > > i see, so this is the way to go: > > >QUESTIONS > >-- > >QuestionID > >QuestionText > > > >POSSIBLE_ANSWERS > >-- > >QuestionID > >AnswerID (A/B/C?) > >AnswerText > > > >ANSWERS > >-- > >QuestionID > >AnswerID > >UserID > > but there is still one problem. not every answer got an ID, > one can also enter free text as an answer, i'd need to > seperate that out in another table again but it's possible of > course. I will do some performance testing on this for large data. > > Regarding my other replies: Because we need to do joins to > get the data from that tables above i thought about creating > a view to transform the table above into the original idea i had: > > id|q1|q2 > 1|a1|a2 > 2|a3|a4 > > is this possible with reasonable performance? i only got very > slow implementations. also see this link: > http://www.wormus.com/aaron/stories/2007/03/23/mysql-restructu > ring-data-for-a-view.html i just thought so, because it would > be very handy to read and update data that way (altough the > updates on that view doesn't seem to be possible at all). > > Fabian > > -- > Fabian Köhler http://www.fabiankoehler.de > > > -----Original Message----- > > From: Jerry Schwartz [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, April 17, 2007 3:50 PM > > To: 'Fabian Köhler'; mysql@lists.mysql.com > > Subject: RE: Max columns in a tabel in MyISAM storage engine > > > > The "right" way to do this would be your second way, where > > there would be > > one row per answer. I can't really grasp how the questions > and answers > > relate to anything else, so I'm making a lot of assumptions. > > > > If you are concerned that this technique is too slow, because > > it involves > > linking two tables, there is an ugly alternative. You could > > store all of > > your answers in a single text field, with some kind of > > delimited format: > > > > "q1=answer1,q2=answer2,q5=answer5" > > > > And then parse it in your program. > > > > Again, I'm not sure where you are going with this. > > > > Regards, > > > > Jerry Schwartz > > Global Information Incorporated > > 195 Farmington Ave. > > Farmington, CT 06032 > > > > 860.674.8796 / FAX: 860.674.8341 > > > > > > > -----Original Message----- > > > From: Fabian Köhler [mailto:[EMAIL PROTECTED] > > > Sent: Monday, April 16, 2007 7:04 PM > > > To: 'mysql@lists.mysql.com' > > > Subject: Max columns in a tabel in MyISAM storage engine > > > > > > 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. > > > > > > 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] > > > > > > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
