On Mon, Feb 7, 2011 at 7:05 PM, Eric Day <e...@oddments.org> 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 _______________________________________________ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
