Adding indexes to frequently joined database columns.

Fixes bug 1006624

Change-Id: I0f1f6903a29cb8d3573987b88f62cd895a11c100

14 files changed, 840 insertions, 0 deletions

diff --git a/nova/db/sqlalchemy/migrate_repo/versions/118_add_indexes_to_agent_builds.py b/nova/db/sqlalchemy/migrate_repo/versions/118_add_indexes_to_agent_builds.py
new file mode 100644
index 000000000..23f7d3cdb
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/118_add_indexes_to_agent_builds.py
@@ -0,0 +1,44 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    # Based on agent_build_get_by_triple
+    # from: nova/db/sqlalchemy/api.py
+    t = Table('agent_builds', meta, autoload=True)
+    i = Index('agent_builds_hypervisor_os_arch_idx',
+              t.c.hypervisor, t.c.os, t.c.architecture)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('agent_builds', meta, autoload=True)
+    i = Index('agent_builds_hypervisor_os_arch_idx',
+              t.c.hypervisor, t.c.os, t.c.architecture)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/119_add_indexes_to_aggregate_metadata.py b/nova/db/sqlalchemy/migrate_repo/versions/119_add_indexes_to_aggregate_metadata.py
new file mode 100644
index 000000000..0e819a59d
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/119_add_indexes_to_aggregate_metadata.py
@@ -0,0 +1,42 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    # Based on aggregate_metadata_get_item
+    # from: nova/db/sqlalchemy/api.py
+    t = Table('aggregate_metadata', meta, autoload=True)
+    i = Index('aggregate_metadata_key_idx', t.c.key)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('aggregate_metadata', meta, autoload=True)
+    i = Index('aggregate_metadata_key_idx', t.c.key)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/120_add_indexes_to_block_device_mapping.py b/nova/db/sqlalchemy/migrate_repo/versions/120_add_indexes_to_block_device_mapping.py
new file mode 100644
index 000000000..432fd91a0
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/120_add_indexes_to_block_device_mapping.py
@@ -0,0 +1,71 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('block_device_mapping', meta, autoload=True)
+
+    # Based on block_device_mapping_update_or_create
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('block_device_mapping_instance_uuid_device_name_idx',
+              t.c.instance_uuid, t.c.device_name)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on block_device_mapping_update_or_create
+    # from: nova/db/sqlalchemy/api.py
+    i = Index(
+            'block_device_mapping_instance_uuid_virtual_name_device_name_idx',
+            t.c.instance_uuid, t.c.virtual_name, t.c.device_name)
+    i.create(migrate_engine)
+
+    # Based on block_device_mapping_destroy_by_instance_and_volume
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('block_device_mapping_instance_uuid_volume_id_idx',
+              t.c.instance_uuid, t.c.volume_id)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('block_device_mapping', meta, autoload=True)
+
+    i = Index('block_device_mapping_instance_uuid_device_name_idx',
+              t.c.instance_uuid, t.c.device_name)
+    i.drop(migrate_engine)
+
+    i = Index(
+            'block_device_mapping_instance_uuid_virtual_name_device_name_idx',
+            t.c.instance_uuid, t.c.virtual_name, t.c.device_name)
+    i.drop(migrate_engine)
+
+    i = Index('block_device_mapping_instance_uuid_volume_id_idx',
+              t.c.instance_uuid, t.c.volume_id)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/121_add_indexes_to_bw_usage_cache.py b/nova/db/sqlalchemy/migrate_repo/versions/121_add_indexes_to_bw_usage_cache.py
new file mode 100644
index 000000000..fcbe49061
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/121_add_indexes_to_bw_usage_cache.py
@@ -0,0 +1,44 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    # Based on bw_usage_get_by_uuids
+    # from: nova/db/sqlalchemy/api.py
+    t = Table('bw_usage_cache', meta, autoload=True)
+    i = Index('bw_usage_cache_uuid_start_period_idx',
+              t.c.uuid, t.c.start_period)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('bw_usage_cache', meta, autoload=True)
+    i = Index('bw_usage_cache_uuid_start_period_idx',
+              t.c.uuid, t.c.start_period)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/122_add_indexes_to_certificates.py b/nova/db/sqlalchemy/migrate_repo/versions/122_add_indexes_to_certificates.py
new file mode 100644
index 000000000..1201ce6be
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/122_add_indexes_to_certificates.py
@@ -0,0 +1,59 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('certificates', meta, autoload=True)
+
+    # Based on certificate_get_all_by_project
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('certificates_project_id_deleted_idx',
+              t.c.project_id, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on certificate_get_all_by_user
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('certificates_user_id_deleted_idx',
+              t.c.user_id, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('certificates', meta, autoload=True)
+
+    i = Index('certificates_project_id_deleted_idx',
+              t.c.project_id, t.c.deleted)
+    i.drop(migrate_engine)
+
+    i = Index('certificates_user_id_deleted_idx',
+              t.c.user_id, t.c.deleted)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/123_add_indexes_to_dns_domains.py b/nova/db/sqlalchemy/migrate_repo/versions/123_add_indexes_to_dns_domains.py
new file mode 100644
index 000000000..6bc0aed91
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/123_add_indexes_to_dns_domains.py
@@ -0,0 +1,44 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    # Based on _dnsdomain_get
+    # from: nova/db/sqlalchemy/api.py
+    t = Table('dns_domains', meta, autoload=True)
+    i = Index('dns_domains_domain_deleted_idx',
+              t.c.domain, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('dns_domains', meta, autoload=True)
+    i = Index('dns_domains_domain_deleted_idx',
+              t.c.domain, t.c.deleted)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/124_add_indexes_to_fixed_ips.py b/nova/db/sqlalchemy/migrate_repo/versions/124_add_indexes_to_fixed_ips.py
new file mode 100644
index 000000000..0ae4a4d51
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/124_add_indexes_to_fixed_ips.py
@@ -0,0 +1,76 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('fixed_ips', meta, autoload=True)
+
+    # Based on network_get_all_by_host
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('fixed_ips_host_idx', t.c.host)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on fixed_ip_get_by_network_host
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('fixed_ips_network_id_host_deleted_idx',
+              t.c.network_id, t.c.host, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on fixed_ip_associate
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('fixed_ips_address_reserved_network_id_deleted_idx',
+              t.c.address, t.c.reserved, t.c.network_id, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('fixed_ips', meta, autoload=True)
+
+    # Based on network_get_all_by_host
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('fixed_ips_host_idx', t.c.host)
+    i.drop(migrate_engine)
+
+    # Based on fixed_ip_get_by_network_host
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('fixed_ips_network_id_host_deleted_idx',
+              t.c.network_id, t.c.host, t.c.deleted)
+    i.drop(migrate_engine)
+
+    # Based on fixed_ip_associate
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('fixed_ips_address_reserved_network_id_deleted_idx',
+              t.c.address, t.c.reserved, t.c.network_id, t.c.deleted)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/125_add_indexes_to_floating_ips.py b/nova/db/sqlalchemy/migrate_repo/versions/125_add_indexes_to_floating_ips.py
new file mode 100644
index 000000000..b953b28b9
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/125_add_indexes_to_floating_ips.py
@@ -0,0 +1,68 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('floating_ips', meta, autoload=True)
+
+    # Based on floating_ip_get_all_by_host
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('floating_ips_host_idx', t.c.host)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on floating_ip_get_all_by_project
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('floating_ips_project_id_idx', t.c.project_id)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on floating_ip_allocate_address
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('floating_ips_pool_deleted_fixed_ip_id_project_id_idx',
+              t.c.pool, t.c.deleted, t.c.fixed_ip_id, t.c.project_id)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('floating_ips', meta, autoload=True)
+
+    i = Index('floating_ips_host_idx', t.c.host)
+    i.drop(migrate_engine)
+
+    i = Index('floating_ips_project_id_idx', t.c.project_id)
+    i.drop(migrate_engine)
+
+    i = Index('floating_ips_pool_deleted_fixed_ip_id_project_id_idx',
+              t.c.pool, t.c.deleted, t.c.fixed_ip_id, t.c.project_id)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/126_add_indexes_to_instance_faults.py b/nova/db/sqlalchemy/migrate_repo/versions/126_add_indexes_to_instance_faults.py
new file mode 100644
index 000000000..3ed8277a6
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/126_add_indexes_to_instance_faults.py
@@ -0,0 +1,44 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    # Based on instance_fault_get_by_instance_uuids
+    # from: nova/db/sqlalchemy/api.py
+    t = Table('instance_faults', meta, autoload=True)
+    i = Index('instance_faults_instance_uuid_deleted_created_at_idx',
+              t.c.instance_uuid, t.c.deleted, t.c.created_at)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('instance_faults', meta, autoload=True)
+    i = Index('instance_faults_instance_uuid_deleted_created_at_idx',
+              t.c.instance_uuid, t.c.deleted, t.c.created_at)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/127_add_indexes_to_instance_type_extra_specs.py b/nova/db/sqlalchemy/migrate_repo/versions/127_add_indexes_to_instance_type_extra_specs.py
new file mode 100644
index 000000000..80ef0f983
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/127_add_indexes_to_instance_type_extra_specs.py
@@ -0,0 +1,44 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    # Based on instance_type_extra_specs_get_item
+    # from: nova/db/sqlalchemy/api.py
+    t = Table('instance_type_extra_specs', meta, autoload=True)
+    i = Index('instance_type_extra_specs_instance_type_id_key_idx',
+              t.c.instance_type_id, t.c.key)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('instance_type_extra_specs', meta, autoload=True)
+    i = Index('instance_type_extra_specs_instance_type_id_key_idx',
+              t.c.instance_type_id, t.c.key)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/128_add_indexes_to_instances.py b/nova/db/sqlalchemy/migrate_repo/versions/128_add_indexes_to_instances.py
new file mode 100644
index 000000000..a429a7685
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/128_add_indexes_to_instances.py
@@ -0,0 +1,96 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('instances', meta, autoload=True)
+
+    # Based on service_get_all_compute_sorted
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('instances_host_deleted_idx',
+              t.c.host, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on instance_get_all_by_reservation
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('instances_reservation_id_idx', t.c.reservation_id)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on instance_get_active_by_window
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('instances_terminated_at_launched_at_idx',
+              t.c.terminated_at, t.c.launched_at)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on security_group_in_use
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('instances_uuid_deleted_idx',
+              t.c.uuid, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on instance_get_all_hung_in_rebooting
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('instances_task_state_updated_at_idx',
+              t.c.task_state, t.c.updated_at)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('instances', meta, autoload=True)
+
+    i = Index('instances_host_deleted_idx',
+              t.c.host, t.c.deleted)
+    i.drop(migrate_engine)
+
+    i = Index('instances_reservation_id_idx', t.c.reservation_id)
+    i.drop(migrate_engine)
+
+    i = Index('instances_terminated_at_launched_at_idx',
+              t.c.terminated_at, t.c.launched_at)
+    i.drop(migrate_engine)
+
+    i = Index('instances_uuid_deleted_idx',
+              t.c.uuid, t.c.deleted)
+    i.drop(migrate_engine)
+
+    i = Index('instances_task_state_updated_at_idx',
+              t.c.task_state, t.c.updated_at)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/129_add_indexes_to_iscsi_targets.py b/nova/db/sqlalchemy/migrate_repo/versions/129_add_indexes_to_iscsi_targets.py
new file mode 100644
index 000000000..e904742ae
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/129_add_indexes_to_iscsi_targets.py
@@ -0,0 +1,57 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('iscsi_targets', meta, autoload=True)
+
+    # Based on iscsi_target_count_by_host
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('iscsi_targets_host_idx', t.c.host)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on volume_allocate_iscsi_target
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('iscsi_targets_host_volume_id_deleted_idx',
+              t.c.host, t.c.volume_id, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('iscsi_targets', meta, autoload=True)
+
+    i = Index('iscsi_targets_host_idx', t.c.host)
+    i.drop(migrate_engine)
+
+    i = Index('iscsi_targets_host_volume_id_deleted_idx',
+              t.c.host, t.c.volume_id, t.c.deleted)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/130_add_indexes_to_key_pairs.py b/nova/db/sqlalchemy/migrate_repo/versions/130_add_indexes_to_key_pairs.py
new file mode 100644
index 000000000..82517e53a
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/130_add_indexes_to_key_pairs.py
@@ -0,0 +1,44 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    # Based on key_pair_get
+    # from: nova/db/sqlalchemy/api.py
+    t = Table('key_pairs', meta, autoload=True)
+    i = Index('key_pair_user_id_name_idx',
+              t.c.user_id, t.c.name)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('key_pairs', meta, autoload=True)
+    i = Index('key_pair_user_id_name_idx',
+              t.c.user_id, t.c.name)
+    i.drop(migrate_engine)
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/131_add_indexes_to_networks.py b/nova/db/sqlalchemy/migrate_repo/versions/131_add_indexes_to_networks.py
new file mode 100644
index 000000000..11a9dde86
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/131_add_indexes_to_networks.py
@@ -0,0 +1,107 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2012 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+from sqlalchemy import Index, MetaData, Table
+from sqlalchemy.exc import IntegrityError
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('networks', meta, autoload=True)
+
+    # Based on network_get_by_bridge
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('networks_bridge_deleted_idx',
+              t.c.bridge, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on network_get_all_by_host
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('networks_host_idx', t.c.host)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on network_query
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('networks_project_id_deleted_idx',
+              t.c.project_id, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on network_get_all_by_uuids
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('networks_uuid_project_id_deleted_idx',
+              t.c.uuid, t.c.project_id, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on network_create_safe
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('networks_vlan_deleted_idx',
+              t.c.vlan, t.c.deleted)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+    # Based on network_get_by_cidr
+    # from: nova/db/sqlalchemy/api.py
+    i = Index('networks_cidr_v6_idx', t.c.cidr_v6)
+    try:
+        i.create(migrate_engine)
+    except IntegrityError:
+        pass
+
+
+def downgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    t = Table('networks', meta, autoload=True)
+
+    i = Index('networks_bridge_deleted_idx',
+              t.c.bridge, t.c.deleted)
+    i.drop(migrate_engine)
+
+    i = Index('networks_host_idx', t.c.host)
+    i.drop(migrate_engine)
+
+    i = Index('networks_project_id_deleted_idx',
+              t.c.project_id, t.c.deleted)
+    i.drop(migrate_engine)
+
+    i = Index('networks_uuid_project_id_deleted_idx',
+              t.c.uuid, t.c.project_id, t.c.deleted)
+    i.drop(migrate_engine)
+
+    i = Index('networks_vlan_deleted_idx',
+              t.c.vlan, t.c.deleted)
+    i.drop(migrate_engine)
+
+    i = Index('networks_cidr_v6_idx', t.c.cidr_v6)
+    i.drop(migrate_engine)