openstack team mailing list archive

[Jay Pipes <jaypipes@xxxxxxxxx>]

On Mon, Feb 7, 2011 at 7:05 PM, Eric Day <eday@xxxxxxxxxxxx> wrote:
> On Mon, Feb 07, 2011 at 06:43:37PM -0500, Jay Pipes wrote:
>> For any of these types of requests, the process is always going to be this:
>>
>> request
>>   -> api node
>>      -> auth plugin get list of entities for some entity
>>   <- api node, for each in list of entities, issue a request to get
>> answer to query
>>
>> And therefore, instead of doing, say, a JOIN on a table, we're
>> splitting the JOIN into many different requests based on this list of
>> entities returned from the auth plugin.
>
> We can still perform a JOIN if the backing data store supports it. I
> imagine the instance table would have no columns for entities and
> instead have:
>
> CREATE TABLE instance (
>  id <some type> primary key
>  ...
> );
>
> CREATE TABLE instance_entity_map (
>  instance_id <some type>,
>  entity_id <some type>,
>  primary key (instance_id, entity_id)
> );
>
> The entities returned from the plugins would have some ID, and we
> can do:
>
> entities = nova.auth.lookup(account_id)
> entity_ids = ','.join([entity.id for entity in entities])
>
> and then:
>
> SELECT * FROM instance_entity_map WHERE entity_id in (<entity_ids>);
>
> or if we want all instance data:
>
> SELECT * FROM instances
> JOIN instance_entity_map ON instance.id=instance_entity_map.instance_id
> WHERE instance_entity_map.entity_id in (<entity_ids>);
>
> Does this solve what you had in mind for the iterating query above,
> or am I off somewhere?

Eric, you and I have a database background. I know you understand that this:

# Executed in the "auth service" or "configuration management
database" as Jorge calls it:
SELECT entity_id FROM entities
WHERE user_id = <request.user_id>

# Executed in the Nova database:
SELECT * FROM instances
JOIN instance_entity_map ON instance.id=instance_entity_map.instance_id
WHERE instance_entity_map.entity_id in (<entity_ids>);

is not the same as this:

# Executed in the Nova database:
SELECT * FROM instances
JOIN instance_entity_map iem ON instance.id=iem.instance_id
JOIN entities ON entities.entity_id = iem.entity_id
JOIN users ON iem.user_id = <request.user_id> # This last join would,
in practice, be a BETWEEN predicate on a self-join to the entities
table

One query on a database versus two queries (one on each database).

Let's not talk about distributed join flattening as if it somehow is a
single query when in fact it isn't.

-jay