in reply to Perl or Mysql to store multiple choices?

It sounds like you're asking if you should compress multiple selections into a single row or not -- with the database select being a given.

Based on that, compressing and decompressing the data (with a join and split) would be my suggestion; that's got to be faster than doing an INSERT/UPDATE and then a SELECT for multiple fields. I expect the code would look cleaner too.

Although I've read benchmarks, I've never used Benchmark, but it would definitely tell you what's faster, and then you'd have the ability to balance that speed with the associated complexity of the code.

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

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:49 UTC
    Yes, that's exactly what I'm talking about. And what I'm thinking.

    When a user selects options 1,4,7,9, and 15 on Monday, either:
    a) store it as 5 separate insertions of update_id - option pairs
    b) first join them into a delimited string and do one insert

    Then on retrieval, either:
    a) select all rows with Monday's update ID (which means a while loop)
    b) select a single row and split the retrieved string

    I expect the first reaction to be "let the DB do its work; it's undoubtedly smarter and faster", but I suspect that the counter-intutitive ins in fact true: that the extra DBI layer, with its preparation and the retrieval loop, is going in fact ot be slower than a pure join or split, especially when it gets more complicate with multiple questions to store and therefore multiple queries to do.




    Forget that fear of gravity,
    Get a little savagery in your life.
[reply]
      punch card don:

      With the speed of today's computers, and the tiny size of your database, speed isn't going to be an issue. Basically, you should write the cleanest code you can, to make maintenance simpler. Only after you have something and it proves to be too slow (which I don't expect to see in this case), do you look for optimizations.

      I'm not advocating being purposefully wasteful...It's just that programmer time is much more expensive than computer time....

      --roboticus

[reply]

In Section Seekers of Perl Wisdom