List:General Discussion« Previous MessageNext Message »
From:Fabian Köhler Date:April 17 2007 11:22am
Subject:Re: Max columns in a tabel in MyISAM storage engine
View as plain text  
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"
<micah@stripped> 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
> >
> >   
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