hello,
i want to update a column name in a table, but the only way for that is
to redo the table
the table have around 15 000 000 records
so i do like this :
ALTER TABLE PICTURE_HASH_ID RENAME TO PICTURE_HASH_ID_OLD;
DROP INDEX PICTURE_HASH_ID_PIC_IDX;
CREATE TABLE PICTURE_HASH_ID(
HASH_ID INTEGER PRIMARY KEY ASC,
SERVER_ID INTEGER,
VOLUME_ID INTEGER,
NEEDLE_ID UNSIGNED INTEGER,
NEEDLE_KEY UNSIGNED BIG INT
);
CREATE UNIQUE INDEX PICTURE_HASH_ID_PIC_IDX on PICTURE_HASH_ID
(SERVER_ID ASC, VOLUME_ID ASC, NEEDLE_ID ASC, NEEDLE_KEY ASC);
INSERT INTO PICTURE_HASH_ID(HASH_ID, SERVER_ID, VOLUME_ID, NEEDLE_ID,
NEEDLE_KEY) SELECT HASH_ID, SERVER_ID, VOLUME_ID, PICTURE_ID,
PICTURE_KEY FROM PICTURE_HASH_ID_OLD;
DROP TABLE PICTURE_HASH_ID_OLD;
but now the speed of the insert/select is completely crazy.
before it's take 100 ms max and now it's take 30 secondes !
this the database definition :
PRAGMA page_size = 1024;
PRAGMA encoding = "UTF-8";
PRAGMA legacy_file_format = 0;
PRAGMA auto_vacuum = NONE;
CREATE VIRTUAL TABLE PICTURE_HASH_1 USING rtree(
HASH_ID INTEGER PRIMARY KEY ASC,
x1_y1_min UNSIGNED TINYINT, x1_y1_max UNSIGNED TINYINT,
x1_y2_min UNSIGNED TINYINT, x1_y2_max UNSIGNED TINYINT,
x1_y3_min UNSIGNED TINYINT, x1_y3_max UNSIGNED TINYINT,
x1_y4_min UNSIGNED TINYINT, x1_y4_max UNSIGNED TINYINT,
x1_y5_min UNSIGNED TINYINT, x1_y5_max UNSIGNED TINYINT
);
CREATE VIRTUAL TABLE PICTURE_HASH_2 USING rtree(
HASH_ID INTEGER PRIMARY KEY ASC,
x2_y1_min UNSIGNED TINYINT, x2_y1_max UNSIGNED TINYINT,
x2_y2_min UNSIGNED TINYINT, x2_y2_max UNSIGNED TINYINT,
x2_y3_min UNSIGNED TINYINT, x2_y3_max UNSIGNED TINYINT,
x2_y4_min UNSIGNED TINYINT, x2_y4_max UNSIGNED TINYINT,
x2_y5_min UNSIGNED TINYINT, x2_y5_max UNSIGNED TINYINT
);
CREATE VIRTUAL TABLE PICTURE_HASH_3 USING rtree(
HASH_ID INTEGER PRIMARY KEY ASC,
x3_y1_min UNSIGNED TINYINT, x3_y1_max UNSIGNED TINYINT,
x3_y2_min UNSIGNED TINYINT, x3_y2_max UNSIGNED TINYINT,
x3_y3_min UNSIGNED TINYINT, x3_y3_max UNSIGNED TINYINT,
x3_y4_min UNSIGNED TINYINT, x3_y4_max UNSIGNED TINYINT,
x3_y5_min UNSIGNED TINYINT, x3_y5_max UNSIGNED TINYINT
);
CREATE VIRTUAL TABLE PICTURE_HASH_4 USING rtree(
HASH_ID INTEGER PRIMARY KEY ASC,
x4_y1_min UNSIGNED TINYINT, x4_y1_max UNSIGNED TINYINT,
x4_y2_min UNSIGNED TINYINT, x4_y2_max UNSIGNED TINYINT,
x4_y3_min UNSIGNED TINYINT, x4_y3_max UNSIGNED TINYINT,
x4_y4_min UNSIGNED TINYINT, x4_y4_max UNSIGNED TINYINT,
x4_y5_min UNSIGNED TINYINT, x4_y5_max UNSIGNED TINYINT
);
CREATE VIRTUAL TABLE PICTURE_HASH_5 USING rtree(
HASH_ID INTEGER PRIMARY KEY ASC,
x5_y1_min UNSIGNED TINYINT, x5_y1_max UNSIGNED TINYINT,
x5_y2_min UNSIGNED TINYINT, x5_y2_max UNSIGNED TINYINT,
x5_y3_min UNSIGNED TINYINT, x5_y3_max UNSIGNED TINYINT,
x5_y4_min UNSIGNED TINYINT, x5_y4_max UNSIGNED TINYINT,
x5_y5_min UNSIGNED TINYINT, x5_y5_max UNSIGNED TINYINT
);
CREATE TABLE PICTURE_HASH_ID(
HASH_ID INTEGER PRIMARY KEY ASC,
SERVER_ID INTEGER,
VOLUME_ID INTEGER,
NEEDLE_ID UNSIGNED INTEGER,
NEEDLE_KEY UNSIGNED BIG INT
);
CREATE UNIQUE INDEX PICTURE_HASH_ID_PIC_IDX on PICTURE_HASH_ID
(SERVER_ID ASC, VOLUME_ID ASC, NEEDLE_ID ASC, NEEDLE_KEY ASC);
and i do only this kind of SQL :
Select
P1.SERVER_ID,
P1.VOLUME_ID,
P1.NEEDLE_ID,
P1.NEEDLE_KEY
from
PICTURE_HASH_1 H1
JOIN PICTURE_HASH_2 H2 ON H2.HASH_ID=H1.HASH_ID
JOIN PICTURE_HASH_3 H3 ON H3.HASH_ID=H1.HASH_ID
JOIN PICTURE_HASH_4 H4 ON H4.HASH_ID=H1.HASH_ID
JOIN PICTURE_HASH_5 H5 ON H5.HASH_ID=H1.HASH_ID
JOIN PICTURE_HASH_ID P1 ON P1.HASH_ID=H1.HASH_ID
where
H1.x1_y1_min <= xxx and
H1.x1_y1_max >= yyy and
...
H5.x5_y5_min <= www and
H5.x5_y5_max >= zzz
following by some insert
insert into PICTURE_HASH_1 ..
insert into PICTURE_HASH_2 ..
insert into PICTURE_HASH_3 ..
insert into PICTURE_HASH_4 ..
insert into PICTURE_HASH_5 ..
insert into PICTURE_HASH_ID ..
before it's return in 100 ms now 30 secondes :(
What i do wrong and how to correct it ?
i m on Windows 2008 R2 64 bit
thanks you by advance
stéphane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
