I'm not sure I understand your database schema. From how I understood it, I created the following SQL schema (in SQLite syntax), which is not denormalized, so it might be suboptimal, but it is as clean as I can imagine it. By "fk" I mean "foreign key", so offered_choice.correct should be a value that can be found in the response.id column.

create table response (
  -- models a possible response
  id    integer primary key,
  text    varchar
);

create table question (
  -- models a possible question
  id    integer primary key,
  text    varchar
);

create table offered_choice (
  -- models the offered choices to a question
  id        integer primary key,
  question    integer,
  choice        integer -- fk into response
);


create table questionnaire (
  id        integer primary key,
  position    integer,
  choice_set    integer, -- fk into offered_choice
  correct    integer -- fk into response
);

create table answer (
  -- models a given answer
  id        integer primary key,
  subject    integer,
  questionlet    integer,
  response    integer
);

create table subject (
  -- models a person taking a test
  id        integer primary key,
  name        varchar()
);

[download]

With these tables filled, and indices at least on every primary key column, your queries become joins over multiple tables, but they should also not be too slow.

Your dataset doesn't seem too large though, so I wonder what you did to actually optimize MySQL performance - MySQL can hold a (read-only) table completely in memory, so you should tell MySQL to do just that - this should speed up your queries by quite a lot too. On the other hand, I still wonder about your current database schema, because blindly throwing around 3000 tables, or 3000 indices doesn't make much sense to me. I'm also not sure if every subject got asked every question and got offered every possible response - my schema allows for mixing the responses offered to every question, but also allows for only one "correct" response to every questionlet.

In reply to Re^3: Does Search::InvertedIndex module live up to billing? by Corion
in thread Does Search::InvertedIndex module live up to billing? 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.