Re: Optimizing a query to out-perform MS Access (easy, right?)
Well, Felix, you've hit the nail bang on the head, as per your usual! I am currently kicking myself for not having tried that earlier -- I did think of it, but didn't think it'd make that much of a difference.
Your exact suggestion (with r_id at the left of the index) didn't knock the time down much but when I tried ADD UNIQUE(a_id,q_id,r_id), it dropped to 1.3 sec! Bin, go. Thank you so much for pointing that out! If it were a snake... but I never would have caught it without your suggestion.
Now I just need to figure out why it works... I'm assuming that my version worked better because it has one of the joined columns on the far left (adding another UNIQUE(q_id,a_id,r_id) knocked another few tenths off). But it looks like the real trick here is the unique index. I didn't know they were that much faster than the regular keys, but it sure looks like it. Back to the manuals for me...
Also, the more simplified queries (even ones that don't look like they should use the covering index!?) are running faster -- around 1.2 sec -- but I'd still like to tighten them up a bit as I think they can be faster still. But that's just loose ends -- that multiple-column index seems to have the kind of big effect that I knew must be possible.
Erin, you're right, I didn't post my RAM, partly because it's so embarassingly low that you all would have lobbed e-tomatoes at me for wasting your time helping me optimize on a prehistoric machine (Pentium 3, and think M, not G...). I went ahead with the optimization on this box because I only needed good performance relative to our current Access system in a similar environment. This query was small enough that it shouldn't have stretched even my RAM too far, and I suspected that the bottleneck was my brain rather than the machine's. Luckily, Felix proved me right. If anything, hopefully this can be an example that MySQL can perform even with really lousy hardware/OS. But now that I'll be able to get my queries optimized correctly, I'll be able to really maximize my resources once I port it to some legitimate hardware. And I know you're right about %nix, btw. We all have dreams... I would still love to hear your server variable tweak ideas, though, for any RAM setup.
Thanks so much to everybody for helping out with this -- you've saved me a huge amount of work and hassle, and I've learned a ton while trying to solve this. I hope that other folks with similar problems can get some use out of this thread in the future.
Kind regards,
Dave