On 04/17/2007 04:18 AM, Fabian Köhler wrote:
> 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
>
Not a big deal. Any performance increase will be marginal, and the ease
of having a standard column type in my opinion outweighs any performance
decrease.
> 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?
>
>
How is this not a processor intensive operation no matter what? If
you're collecting this much data, you have to deal with that much data,
the key is to arrange is to that the database knows something about the
relationships between the data (questions * answers) in this case so
that you can generate efficient queries.
> 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?
>
Yes, with either a VIEW or a JOIN.
> Are other dbs like oracle better on doing joins on such large tables?
>
>
I can't speak for MySQL 5.0 because it's been a while since I've done
performance tests, but the old 3.x versions of MySQL would perform much
better than oracle with simple joins.
> 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.
>
>
If you're doing that, why not just make a flat-file database of the
answers? That would be much faster still than dealing with the added
overhead of a SQL server. You're ignoring the advantages of having a SQL
setup in this case anyhow.
-Micah
