Re: Query that allows for missing rows

[Rick James]

Groan.

1. I insist that you turn the subqueries into JOINs. If you don't even a small dataset will be so miserably slow that you will come back to the forum with a performance problem. (I predict that in doing this transformation, your original question will be accidentally solved.)

2. Now that it is obvious that you have a key-value (EAV) store, you are subjected to being berated about that. Sure, it's a convenient way to have an open-ended schema. But performance can be terrible. And, as you see, the queries are awfully clumsy.

If a field in your K-V store needs to be selected on (WHERE ...) then include it in the main table. Doing this (with or without an index on that field) will greatly improve performance and simplicity of queries. The rest of the K-V fields can be left wherever. I like to toss them into a JSON field. Perl and PHP have convenient functions for converting arbitrary structures to/from JSON. If the table is not too big, then a TEXT (charset utf8) column should be fine. If it is a big table, then I recommend compressing an putting into a BLOB column.

There are all sorts of offerings out there that specialize in K-V stores; see NoSQL, MongoDB, CouchDB, etc. They may work better for your application, but they have too many limitations for me.