MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query that allows for missing rows
Posted by: Rick James
Date: April 17, 2011 10:04AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
3920
April 15, 2011 10:49AM
Re: Query that allows for missing rows
1200
April 17, 2011 10:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.