List:General Discussion« Previous MessageNext Message »
From:Micah Stevens Date:April 17 2007 6:39am
Subject:Re: Max columns in a tabel in MyISAM storage engine
View as plain text  
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