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