From: Ian Beckwith <ianb@erislabs.net>
Date: Mon, 18 Oct 2010 03:50:19 +0000 (+0100)
Subject: tweak indexes in schema
X-Git-Tag: debian/1.0-1~70
X-Git-Url: https://erislabs.net/gitweb/?p=id3fs.git;a=commitdiff_plain;h=5bdcd09be9f28fd2a181324e33f026f6f097b05a

tweak indexes in schema
---

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index deaa16e..11b5525 100644
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -362,7 +362,7 @@ sub unindex
 sub prune_directories
 {
     my($self)=@_;
-    my $sql=("SELECT name, id FROM paths ORDER BY name\n");
+    my $sql=("SELECT name, id FROM paths\n");
     my $pathsref=$self->cmd_rows($sql);
     my @ids=();
     for my $pathpair (@$pathsref)
@@ -565,15 +565,16 @@ CREATE TABLE files_x_tags (
     FOREIGN KEY(tags_id)  REFERENCES tags(id)  ON DELETE CASCADE ON UPDATE CASCADE
 );
 
-CREATE INDEX idx_files_id ON files (id)
-
 CREATE INDEX idx_fxt_both ON files_x_tags (files_id, tags_id)
 
-CREATE INDEX idx_fxt_files ON files_x_tags (files_id)
-
 CREATE INDEX idx_fxt_tags ON files_x_tags (tags_id)
 
-CREATE INDEX idx_tags_id ON tags (id)
+CREATE INDEX idx_files_id_name ON files (id, name)
 
-CREATE INDEX idx_tags_name ON tags (name)
+CREATE INDEX idx_files_name_id ON files (name, id)
 
+CREATE INDEX idx_tags_id_parent_name ON tags (id, parents_id, name)
+
+CREATE INDEX idx_tags_parent_id_name ON tags (parents_id, id, name)
+
+CREATE INDEX idx_tags_name ON tags (name)