From 1eddfed90792799379b7fb17f4f29c493ba062c9 Mon Sep 17 00:00:00 2001 From: Peng Huang Date: Mon, 10 May 2010 15:49:57 +0800 Subject: [PATCH] Drop indexes before create indexes. --- data/db/create_index.sql | 99 +++++++++++++++++++++++++++++++++--------------- 1 file changed, 68 insertions(+), 31 deletions(-) diff --git a/data/db/create_index.sql b/data/db/create_index.sql index 7d92135..d40ed02 100644 --- a/data/db/create_index.sql +++ b/data/db/create_index.sql @@ -1,31 +1,68 @@ -CREATE INDEX IF NOT EXISTS index_0_0 ON py_phrase_0(s0, y0); -CREATE INDEX IF NOT EXISTS index_1_0 ON py_phrase_1(s0, y0, s1, y1); -CREATE INDEX IF NOT EXISTS index_1_1 ON py_phrase_1(s0, s1, y1); -CREATE INDEX IF NOT EXISTS index_2_0 ON py_phrase_2(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_2_1 ON py_phrase_2(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_3_0 ON py_phrase_3(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_3_1 ON py_phrase_3(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_4_0 ON py_phrase_4(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_4_1 ON py_phrase_4(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_5_0 ON py_phrase_5(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_5_1 ON py_phrase_5(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_6_0 ON py_phrase_6(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_6_1 ON py_phrase_6(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_7_0 ON py_phrase_7(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_7_1 ON py_phrase_7(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_8_0 ON py_phrase_8(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_8_1 ON py_phrase_8(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_9_0 ON py_phrase_9(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_9_1 ON py_phrase_9(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_10_0 ON py_phrase_10(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_10_1 ON py_phrase_10(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_11_0 ON py_phrase_11(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_11_1 ON py_phrase_11(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_12_0 ON py_phrase_12(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_12_1 ON py_phrase_12(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_13_0 ON py_phrase_13(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_13_1 ON py_phrase_13(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_14_0 ON py_phrase_14(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_14_1 ON py_phrase_14(s0, s1, s2, y2); -CREATE INDEX IF NOT EXISTS index_15_0 ON py_phrase_15(s0, y0, s1, y1, s2, y2); -CREATE INDEX IF NOT EXISTS index_15_1 ON py_phrase_15(s0, s1, s2, y2); +/* drop indexes */ +DROP INDEX IF EXISTS index_0_0; +DROP INDEX IF EXISTS index_1_0; +DROP INDEX IF EXISTS index_1_1; +DROP INDEX IF EXISTS index_2_0; +DROP INDEX IF EXISTS index_2_1; +DROP INDEX IF EXISTS index_3_0; +DROP INDEX IF EXISTS index_3_1; +DROP INDEX IF EXISTS index_4_0; +DROP INDEX IF EXISTS index_4_1; +DROP INDEX IF EXISTS index_5_0; +DROP INDEX IF EXISTS index_5_1; +DROP INDEX IF EXISTS index_6_0; +DROP INDEX IF EXISTS index_6_1; +DROP INDEX IF EXISTS index_7_0; +DROP INDEX IF EXISTS index_7_1; +DROP INDEX IF EXISTS index_8_0; +DROP INDEX IF EXISTS index_8_1; +DROP INDEX IF EXISTS index_9_0; +DROP INDEX IF EXISTS index_9_1; +DROP INDEX IF EXISTS index_10_0; +DROP INDEX IF EXISTS index_10_1; +DROP INDEX IF EXISTS index_11_0; +DROP INDEX IF EXISTS index_11_1; +DROP INDEX IF EXISTS index_12_0; +DROP INDEX IF EXISTS index_12_1; +DROP INDEX IF EXISTS index_13_0; +DROP INDEX IF EXISTS index_13_1; +DROP INDEX IF EXISTS index_14_0; +DROP INDEX IF EXISTS index_14_1; +DROP INDEX IF EXISTS index_15_0; +DROP INDEX IF EXISTS index_15_1; + +/* create indexes */ +CREATE INDEX index_0_0 ON py_phrase_0(s0, y0); +CREATE INDEX index_1_0 ON py_phrase_1(s0, y0, s1, y1); +CREATE INDEX index_1_1 ON py_phrase_1(s0, s1, y1); +CREATE INDEX index_2_0 ON py_phrase_2(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_2_1 ON py_phrase_2(s0, s1, s2, y2); +CREATE INDEX index_3_0 ON py_phrase_3(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_3_1 ON py_phrase_3(s0, s1, s2, y2); +CREATE INDEX index_4_0 ON py_phrase_4(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_4_1 ON py_phrase_4(s0, s1, s2, y2); +CREATE INDEX index_5_0 ON py_phrase_5(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_5_1 ON py_phrase_5(s0, s1, s2, y2); +CREATE INDEX index_6_0 ON py_phrase_6(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_6_1 ON py_phrase_6(s0, s1, s2, y2); +CREATE INDEX index_7_0 ON py_phrase_7(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_7_1 ON py_phrase_7(s0, s1, s2, y2); +CREATE INDEX index_8_0 ON py_phrase_8(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_8_1 ON py_phrase_8(s0, s1, s2, y2); +CREATE INDEX index_9_0 ON py_phrase_9(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_9_1 ON py_phrase_9(s0, s1, s2, y2); +CREATE INDEX index_10_0 ON py_phrase_10(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_10_1 ON py_phrase_10(s0, s1, s2, y2); +CREATE INDEX index_11_0 ON py_phrase_11(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_11_1 ON py_phrase_11(s0, s1, s2, y2); +CREATE INDEX index_12_0 ON py_phrase_12(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_12_1 ON py_phrase_12(s0, s1, s2, y2); +CREATE INDEX index_13_0 ON py_phrase_13(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_13_1 ON py_phrase_13(s0, s1, s2, y2); +CREATE INDEX index_14_0 ON py_phrase_14(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_14_1 ON py_phrase_14(s0, s1, s2, y2); +CREATE INDEX index_15_0 ON py_phrase_15(s0, y0, s1, y1, s2, y2); +CREATE INDEX index_15_1 ON py_phrase_15(s0, s1, s2, y2); + +/* optimize database */ +VACUUM; -- 2.7.4