Re: Perl or Mysql to store multiple choices?

A database with 250 000 rows is considered small in DBA circles. Use the database for what it's good for - don't reinvent the wheel. The developers at MySQL are probably better at writing efficient queries than you are, especially since they use C to do so. Store as much as you can in a well laid out database, with as many columns as make sense - it'll be faster now, and it will certainly be faster in the future when you think of a new query that you want to execute.

Comment on Re: Perl or Mysql to store multiple choices?

Replies are listed 'Best First'.
Re^2: Perl or Mysql to store multiple choices? by punch_card_don (Curate) on Mar 29, 2007 at 21:42 UTC
Yes, very sensible replies, both. But now let me complicate things: There are four questions, each with 5 or 10 possible choices, and multiple choices are allowed. Each update, the user answers all four questions. So, I could have four separate tables, Q_1 to Q_4, each with 'update_id' and 'option' columns. To retirieve the selections of Monday, I have either four queries to create, prepare, execute and retrieve, or a four-table join to do. Versus one table with update_id and Q_1 to Q_4 columns, from which I do a single query and four splits. I'm unsure that four queries, or a four table join, is going to be faster than a single query. Each query is a call to the DBI - an extra layer. Whereas a split is pure Perl. But I agree that it's certainly not a purist's approach to database design. Forget that fear of gravity, Get a little savagery in your life.	[reply]

Replies are listed 'Best First'.

Re^2: Perl or Mysql to store multiple choices?
by punch_card_don (Curate) on Mar 29, 2007 at 21:42 UTC

But now let me complicate things: There are four questions, each with 5 or 10 possible choices, and multiple choices are allowed. Each update, the user answers all four questions.

So, I could have four separate tables, Q_1 to Q_4, each with 'update_id' and 'option' columns. To retirieve the selections of Monday, I have either four queries to create, prepare, execute and retrieve, or a four-table join to do. Versus one table with update_id and Q_1 to Q_4 columns, from which I do a single query and four splits.

I'm unsure that four queries, or a four table join, is going to be faster than a single query. Each query is a call to the DBI - an extra layer. Whereas a split is pure Perl.

But I agree that it's certainly not a purist's approach to database design.

Forget that fear of gravity,
Get a little savagery in your life.

[reply]