I've been tinkering with my database design over the last few months
and today have found something significant that I wanted to share.
It looks as though the order of the primary key index matters. I had
originally had the order set the way I would typically think of the
data if I were to look it up by hand (subject, arm, repetition). But
then, I switched the order so that the field "repetition" was first.
My queries sped up several orders of magnitude! (The cost went from
something like 295,000 to 295). I think this is because there are many
more repetitions than subjects. So at least for me, the primary key
should be order so that the fields with more variance are listed
first.
Perhaps, this is known already to SQL gurus, but I hadn't come across
it in reading about database optimization.
-Tony