From a911a4b33d3eaa19c1787be3c59143d159dcd8fe Mon Sep 17 00:00:00 2001 From: Claudio Sacerdoti Coen Date: Mon, 29 Aug 2005 11:46:28 +0000 Subject: [PATCH] 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. --- helm/ocaml/metadata/sqlStatements.ml | 25 ++++++++++--------------- 1 file changed, 10 insertions(+), 15 deletions(-) diff --git a/helm/ocaml/metadata/sqlStatements.ml b/helm/ocaml/metadata/sqlStatements.ml index 35abac28b..2e0e5e6a6 100644 --- 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 -- 2.39.2