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.