[PATCH] [db] Create indexes for (pkg_name, version) in suite_{sources,binaries}
These two indexes make faster joins between sources and suite_sources,
and binaries and suite_binaries, since the join key will be indexed on
both sides. The difference is particularly noticeable for the binaries,
since suite_binaries has more than half a million rows.
---
Hello,
this patch depends on my previous one with the version -> source_version
rename. I've also verified it indeed makes a difference.
Cheers,
db/db.sql | 4 ++++
1 files changed, 4 insertions(+), 0 deletions(-)
diff --git a/db/db.sql b/db/db.sql
index ac29183..d5ca20d 100644
--- a/db/db.sql
+++ b/db/db.sql
@@ -304,6 +304,8 @@ CREATE TABLE suite_sources (
ON DELETE CASCADE
);
+CREATE INDEX suite_sources_src_ver_idx ON sources (source, source_version);
+
COMMENT ON TABLE suite_sources IS 'Source packages contained within a suite';
COMMENT ON COLUMN suite_sources.source IS 'Source package name';
COMMENT ON COLUMN suite_sources.source_version IS 'Source package version number';
@@ -328,6 +330,8 @@ CREATE TABLE suite_binaries (
ON DELETE CASCADE
);
+CREATE INDEX suite_binaries_pkg_ver_idx ON suite_binaries (package, version);
+
COMMENT ON TABLE suite_binaries IS 'Binary packages contained within a suite';
COMMENT ON COLUMN suite_binaries.package IS 'Binary package name';
COMMENT ON COLUMN suite_binaries.version IS 'Binary package version number';
--
1.6.2.272.g44c5b
Reply to: