List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 17 2007 11:32am
Subject:Re: Max columns in a tabel in MyISAM storage engine
View as plain text  
Hi,

>> 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

? why a text?

Think:

QUESTIONS
--
QuestionID
QuestionText

POSSIBLE_ANSWERS
--
QuestionID
AnswerID (A/B/C?)
AnswerText

ANSWERS
--
QuestionID
AnswerID
UserID


>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:
>
>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?

Getting the answered questions from 100 users should be fast if you
use an index on UserID in ANSWERS.

>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.

Bad idea, if you add a question, you have to modify your metadata
AND your queries.

This is not how it's supposed to work.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

If you can't dazzle em with brilliance, baffle em with bullshit

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