WARNING: this commit changes the DB representation; you need to alter the

         DB by hand after this commit

- h_position: varchar(255) -> varchar(62)
- h_sort:     varchar(255) -> varchar(6)
- several indexes have been redesigned from scratch.
  Motivation: MySql is not able to exploit two indexes (on columns c1 and c2)
  in a query of type "select ... from table where c1 = x and c2 = y".
  Only one of the two indexes is exploited; the other test is done on each line.
  My commit introduces a single index on c1 AND c2 in place of two indexes.

WARNING: it is sure that a few queries (e.g. match) are greatly optimized by
this commit. On the contrary I ignore if the performance of other queries drops.
Moreover, I have not optimized yet the indexes over the hits and count tables.

diff --git a/helm/ocaml/metadata/sqlStatements.ml b/helm/ocaml/metadata/sqlStatements.ml
index 35abac28b4fa58b7133e1fbf93f0af7bb2a95500..2e0e5e6a660ebaca8e18cd8dd9a16e7b4f2366b7 100644 (file)
--- a/helm/ocaml/metadata/sqlStatements.ml
+++ b/helm/ocaml/metadata/sqlStatements.ml
@@ -32,22 +32,22 @@ let sprintf_refObj_format name = [
 sprintf "CREATE TABLE %s (
     source varchar(255) binary not null,
     h_occurrence varchar(255) binary not null,
-    h_position varchar(255) binary not null,
+    h_position varchar(62) binary not null,
     h_depth integer
 );" name]
 
 let sprintf_refSort_format name = [
 sprintf "CREATE TABLE %s (
     source varchar(255) binary not null,
-    h_position varchar(255) binary not null,
+    h_position varchar(62) binary not null,
     h_depth integer not null,
-    h_sort varchar(255) binary not null
+    h_sort varchar(5) binary not null
 );" name]
 
 let sprintf_refRel_format name = [
 sprintf "CREATE TABLE %s (
     source varchar(255) binary not null,
-    h_position varchar(255) binary not null,
+    h_position varchar(62) binary not null,
     h_depth integer not null
 );" name]
 
@@ -86,12 +86,10 @@ let sprintf_count_drop name = [sprintf "DROP TABLE %s;" name]
 (* INDEXES *)
 
 let sprintf_refObj_index name = [
-sprintf "CREATE INDEX %s_source ON %s (source);" name name ;
-sprintf "CREATE INDEX %s_target ON %s (h_occurrence);" name name ;
-sprintf "CREATE INDEX %s_position ON %s (h_position);" name name ]
+sprintf "CREATE INDEX %s_index ON %s (source(219),h_occurrence(219),h_position);" name name ]
 
 let sprintf_refSort_index name = [
-sprintf "CREATE INDEX %s_source ON %s (source);" name name]
+sprintf "CREATE INDEX %s_index ON %s (source,h_sort,h_position,h_depth);" name name]
 
 let sprintf_objectName_index name = [
 sprintf "CREATE INDEX %s_value ON %s (value);" name name]
@@ -107,16 +105,13 @@ sprintf "CREATE INDEX %s_hypothesis ON %s (hypothesis);" name name;
 sprintf "CREATE INDEX %s_statement ON %s (statement);" name name]
  
 let sprintf_refRel_index name = [
-sprintf "CREATE INDEX %s_source ON %s (source);" name name
-]
+sprintf "CREATE INDEX %s_index ON %s (source,h_position,h_depth);" name name]
 
 let sprintf_refObj_index_drop name = [
-sprintf "DROP INDEX %s_source ON %s;" name name ;
-sprintf "DROP INDEX %s_target ON %s;" name name ;
-sprintf "DROP INDEX %s_position ON %s;" name name ]
+sprintf "DROP INDEX %s_index ON %s;" name name ]
 
 let sprintf_refSort_index_drop name = [
-sprintf "DROP INDEX %s_source ON %s;" name name ]
+sprintf "DROP INDEX %s_index ON %s;" name name ]
 
 let sprintf_objectName_index_drop name = [
 sprintf "DROP INDEX %s_value ON %s;" name name]
@@ -132,7 +127,7 @@ sprintf "DROP INDEX %s_hypothesis ON %s;" name name;
 sprintf "DROP INDEX %s_statement ON %s;" name name]
  
 let sprintf_refRel_index_drop name = [
-sprintf "DROP INDEX %s_source ON %s;" name name]
+sprintf "DROP INDEX %s_index ON %s;" name name]
 
 let sprintf_rename_table oldname newname = [
 sprintf "RENAME TABLE %s TO %s;" oldname newname