List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:April 17 2007 4:01pm
Subject:RE: Max columns in a tabel in MyISAM storage engine
View as plain text  
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-----
> Sent: Tuesday, April 17, 2007 10:09 AM
> To: 'Jerry Schwartz'
> Cc: 'mysql@stripped'
> 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
>
> --
>
> > -----Original Message-----
> > From: Jerry Schwartz [mailto:jschwartz@stripped]
> > Sent: Tuesday, April 17, 2007 3:50 PM
> > 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-----
> > > Sent: Monday, April 16, 2007 7:04 PM
> > > To: 'mysql@stripped'
> > > 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
> > >
> > > --
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/mysql?unsub=1
> > >
> > >
> >
> >
> >
> >
>



Thread
Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineViSolve DB Team17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineMicah Stevens17 Apr
    • Re: Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
      • Re: Max columns in a tabel in MyISAM storage engineMicah Stevens17 Apr
    • Re: Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
    • Re: Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineStijn Verholen17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineMartijn Tonies17 Apr
  • RE: Max columns in a tabel in MyISAM storage engineJerry Schwartz17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineMartijn Tonies17 Apr
    • RE: Max columns in a tabel in MyISAM storage engineJerry Schwartz17 Apr
  • Re: Max columns in a tabel in MyISAM storage engineMartijn Tonies17 Apr
    • RE: Max columns in a tabel in MyISAM storage engineJerry Schwartz17 Apr
RE: Max columns in a tabel in MyISAM storage engineFabian Köhler17 Apr
  • RE: Max columns in a tabel in MyISAM storage engineJerry Schwartz17 Apr