openstack team mailing list archive

[Eric Day <eday@xxxxxxxxxxxx>]

On Mon, Feb 07, 2011 at 08:50:58PM -0500, Jay Pipes wrote:
> Eric, you and I have a database background. I know you understand that this:

Of course, but the first pair of queries is not as bad as a query
for every entity ID returned, which was in one of the previous emails
(the main thing I was trying to address).

There are other indexing tricks we can do as well, but lets not bother
pre-optimizing in email pseudo code. :)

-Eric

> # 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