Greetings!
I have an old db with lots of data and the one of the main tables has a
messed up index. For example,
sqlite> select id from LSOpenProjects where id > 13460 and id < 115520;
13461
13462
13463
13464
13758
115516
115517
115518
115519
sqlite>
As you can see, the id indexing is broken and there is a huge
discrepancy in the sequential id. The question is, does this cause a
slow down for queries? For example, on this query,
BEGIN;
INSERT OR REPLACE INTO OpenProjects
SELECT * FROM client.OpenProjects
WHERE id IN
(
SELECT id FROM client.OpenProjects
WHERE
client.OpenProjects.id = id AND
client.OpenProjects.ProjID <= 133560 AND
client.OpenProjects.XtraB > '2017-02-10 00:00:00' -- change
);
END;
There are indexes on id, ProjID, and XtraB:
CREATE INDEX OpPid ON LSOpenProjects (ProjID);
CREATE INDEX XtraBLSOpenProjects ON LSOpenProjects (XtraB);
CREATE TABLE LSOpenProjects
(
id integer primary key, ProjID integer,...XtraB...
);
So, can that slow down the INSERT? I know that I can turn off the
indexes on the receiving end, and recreate them, but wouldn't that cause
more slow down? Just want to try to speed up these updates, since the
updates lock the main DB and the other users have to wait, and the
complaints are coming more frequently. :-) Thanks.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
