List:General Discussion« Previous MessageNext Message »
From:Stijn Verholen Date:April 17 2007 5:00am
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.
>
> 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
>
>   
Definately curtain number two. It allows you to save space in case of 
not answered questions, and also to extend your database to be able to 
handle different query series (i.e. with a different number of questions).
The number of database queries depends you need to make to extract the 
data, depends on the number of question series (or alternitavely the 
number of users), not the number of answers. You can select all answers 
in a single query.
You'll need  a left join in that query.

hth


-- 


metastable
Stijn Verholen
Camille Huysmanslaan 114, bus 2
B-2020 ANTWERPEN
+32 (0)3 707 08 08
+32 (0)473 47 62 88
info@stripped
http://www.metastable.be

BTW-BE 0873.645.643
bankrek.nr. ING 363-0106543-77

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