[Izkata]

> Pull that garbage out of the json and into a couple of tables and you'd be much faster.

It looks to me like a user-generated search query, not normalized data. A blob column makes perfect sense if this is the case, and pulling it out into other tables/columns would significantly overcomplicate everything else.

[cogman10]

More complicated than a custom hashing scheme and required ordering?

I don't buy that. Tables aren't complex.

[Izkata]

Tables aren't complex, but this particular use can get there. Off the top of my head:

1) Complexity inherent in search criteria: Multiple values per field and whether it's AND or OR. (This can go much much further, as ours does, but I'm limiting it here to what's in the blog post)

2) Centralization of criteria: Multiple tables pretty much means a table per field, which are only related by foreign keys back to the central table.

3) Following on from 2, since there's no central location to identify all the relevant tables, it's much easier to miss one in a JOIN.

4) Following on from 1: Serialization/deserialization, for creating new entries and populating the submission form for an "edit" mode. (At minimum you'll have to reconstruct the multiple-values-per-field situation from the tables)

5) Following on from 3 and 4, alternate uses such as adding a search description derived from the criteria.

There's easy ways around some of this, such as in (4) forcing all the app code to go through the same serialization/deserialization (which sidesteps most of the others), but that's no guarantee that there won't be stray code accessing the tables directly for another reason (hitting on (3)), and almost completely erases the benefit of fitting the criteria into a table relation. It's essentially the same as just using a blob field, except a lot more complicated.

Sometimes blob fields really are the right choice, even in a relational database.