Quantcast

Could missing indexes ids cause slow queries?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Could missing indexes ids cause slow queries?

jose isaias cabrera-3

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Could missing indexes ids cause slow queries?

James K. Lowden
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;
> 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.  

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.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...