Thanks, all, for the help & direction.

I'm trying to put it into practice. Here's the situation:

I have ~20,000 users, each reporting an average of 600 characteristics out of a possible 3,000 characteristics. The objective of this system is to count users who correspond to various collections of characteristics, in the general form:

count all users who reported characteristics a, b &c

I've created a two-column databse that looks like this:

characteristic_id (int), user_id(int)

with no indexes to start, as a baseline. So, for the average user, there are 600 rows, that makes 12-million rows in total.

Following your suggestions, I connected by telnet to execute a few queries.

select count(*) from theTable
correctly reports 12,000,000 rows in 0.00s

select count(*) from theTable where characteristic_id = 123
reports in 0.02s

At this point I'm thinking this is really great. But then

select count(*) from theTable where user_id = 456
reports in 10.77s

And then applying VSarkiss' excellent style:

select count(*) from theTable A inner join theTable B on A.user_id = B.user_id where A.characteristic_id = 123 and B.characteristic_id = 124
reports in 3min 23s!

So - in effect, nothing to do with Perl.

But a new problem. It takes several dozen such queries to compile the report data I need. Clearly 3-minutes per query is unacceptable.

Personally, I'm amazed by the magnitude of the increase in response time.

Sooooooo - dare I hope to see a two-orders-of-magnitude decrease in response time by adding indexes? Or do you think my database design is fubar?

In reply to Re: Does DBI handle table alias? by punch_card_don
in thread Does DBI handle table alias? 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.