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;
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,
INSERT OR REPLACE INTO OpenProjects
SELECT * FROM client.OpenProjects
WHERE id IN
SELECT id FROM client.OpenProjects
client.OpenProjects.id = id AND
client.OpenProjects.ProjID <= 133560 AND
client.OpenProjects.XtraB > '2017-02-10 00:00:00' -- change
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.
On Mon, 13 Feb 2017 22:22:24 +0000
jose isaias cabrera <[hidden email]> wrote:
> sqlite> select id from LSOpenProjects where id > 13460 and id <
> sqlite> 115520;
> As you can see, the id indexing is broken and there is a huge
> discrepancy in the sequential id.
That doesn't not show a broken index or any discrepancy.
I guess you defined id as autoincrement and believe that to guarantee
sequentially generated values. There are many valid reasons observed
values in such a column will not be sequential; DELETE is only one.