I also thought about creating a "materialized view" with mysql by doing:
create table vanswers (select ... query to get the table in the
or a stored procedure which generates a table like the one below, but
all solutions seem to be slow like hell due to the high large joins
which are necessary to create such tables.
On Mon, 16 Apr 2007 23:39:40 -0700, "Micah Stevens"
> Fabian Köhler wrote:
> > Hello,
> > i have table with answers to questions. Every answer is a column in the table.
> > 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
> 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.
> int autoincrement questionID
> int autoincrement answerID
> int questionID
> 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
> > 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