List:General Discussion« Previous MessageNext Message »
From:Fabian Köhler Date:April 17 2007 11:18am
Subject:Re: Max columns in a tabel in MyISAM storage engine
View as plain text  
Thanks for all the input. The problem i have with this idea:

> 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

is, that
1) i must define the column with the value of the answer as a TEXT to
cover all possible answers, altough a SMALLINT might fit it better. but
i can live with that
2) i think it's slow when i have lots of data. i'm currently not at home
to do performance tests, but let's assume i have 2000 questions, then i
collect by user input 2000 answers to every question by 10000 different
users (a realistic scenario for our application) and store it in a table
like this:

ANSWERS
int autoincrement answerID
int userID
int questionID
text answer 

i then have 2000*10000 = 20 mill. rows in ANSWERS. now let's assume i
want to select all answers to all questions for 100 random users at
once. The only solution i see here for one query is a large join which
will take really, really long to get the data even with proper indexes
or did i do sth. wrong?

is there a way to provide a view on these two tables (QUESTIONS and
ANSWERS) that simulate the original table i intended, which is actually
fast even with large amounts of data?

Are other dbs like oracle better on doing joins on such large tables? 

So i really like the idea of having one large table with many columns
because it's really fast and i can define each column properly but i
currently see no option to go this way but with that way there will
alway be a limit to the max. amount of answers in the system.

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