You should add an index on the response column of DB 2. Alternately try to reverse the primary key, make it response/respondant.

Think about how the database has to execute the query. It needs to select all rows with one response and match them up with all rows with another response by respondant. How can the database search for all rows with one response? Well it can either walk the whole table, or it can walk the index looking at every respondant to see if it has a response. Both involve essentially searching the whole table for each response.

I don't think that it will get you to where you want to be. But it should get you from DB 2's current performance to something close to DB 3's performance with a lot less mess.

You should also be able to find some MySQL tuning parameters (look for things like how much shared memory it uses) that improve its performance more.

But still thospel remains correct. Relational databases involve a lot of overhead, and can be beaten on performance. What they win on is simplicity, maintainability, and the fact that it is easier to extend and modify a complex query than a complex piece of carefully optimized code. If those wins are not important to you, and (after careful benchmarking) you find that you can beat the database, the database is not always the best choice.

In reply to Re: Basic Perl trumps DBI? Or my poor DB design? by tilly
in thread Basic Perl trumps DBI? Or my poor DB design? by punch_card_don

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.