Is rootPage a reasonable estimate for index age?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Is rootPage a reasonable estimate for index age?

Deon Brewis
If I have 2 indexes 'A' and 'B', can I use rootPage to determine which one of them is older?

This is for a dynamic index sweep to decide which one to drop.

It doesn't matter if it's sometimes wrong, if the index is needed again it will be re-created. I just don't want to get into a situation where we keep dropping and recreating the newest index rather than the oldest one (we use a set of 16 dynamic indexes).


This seems to work for me, or is it just because my database has been vacuumed recently and if a database has a lot of empty pages it will add new indexes to smaller pages than older indexes?

- Deon
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is rootPage a reasonable estimate for index age?

Richard Hipp-3
On 8/7/19, Deon Brewis <[hidden email]> wrote:
> If I have 2 indexes 'A' and 'B', can I use rootPage to determine which one
> of them is older?
>
> This seems to work for me, or is it just because my database has been
> vacuumed recently and if a database has a lot of empty pages it will add new
> indexes to smaller pages than older indexes?

It probably only works because your DB is vacuumed.

Seems to me that the rowid of the sqlite_master table entry for your
index would be a more reliable indicator.  Larger rowids are newer.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is rootPage a reasonable estimate for index age?

Deon Brewis
Yeah ok that makes more sense.

Sorry, that was a bit obvious - late night.

Thanks!
- Deon

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Richard Hipp
Sent: Wednesday, August 7, 2019 11:32 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Is rootPage a reasonable estimate for index age?

On 8/7/19, Deon Brewis <[hidden email]> wrote:
> If I have 2 indexes 'A' and 'B', can I use rootPage to determine which
> one of them is older?
>
> This seems to work for me, or is it just because my database has been
> vacuumed recently and if a database has a lot of empty pages it will
> add new indexes to smaller pages than older indexes?

It probably only works because your DB is vacuumed.

Seems to me that the rowid of the sqlite_master table entry for your index would be a more reliable indicator.  Larger rowids are newer.

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