create index implies analyze?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

create index implies analyze?

Nelson, Erik - 2
If I am finished making inserts into a table, does the act of creating an index also populate the index?  That is, do I need to separately run 'analyze'?

My impression was that there was no need to call analyze unless rows had been inserted after index creation.

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
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: [EXTERNAL] create index implies analyze?

Hick Gunter
CREATE INDEX will populate the index with references to all of the rows in the table. Bulk loads may run considerably faster if no indices are present at load time (not yet created or dropped beforehand), but created right after the data has been inserted. If you run INSERT or UPDATE statements later, they will also update the available indices too.

ANALYZE does NOT "populate" an index. It attempts to deduce the "shape" of your data, so that a faster query plan may be selected for queries that require ordering and/or joining several tables.

Running ANALYZE is only necessary if the "shape" of your data has changed, e.g. you have loaded a large dataset that is different from what you had before. Note that it is also possible to extract the results of ANALYZE from a "production sized" database and insert them into an empty database (with identical schema, of course), so that running ANALYZE is not required at all even after loading production data.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nelson, Erik - 2
Gesendet: Mittwoch, 20. Dezember 2017 16:21
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] create index implies analyze?

If I am finished making inserts into a table, does the act of creating an index also populate the index?  That is, do I need to separately run 'analyze'?

My impression was that there was no need to call analyze unless rows had been inserted after index creation.

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: create index implies analyze?

David Raymond
In reply to this post by Nelson, Erik - 2
Analyze has to be run separately. Create Index doesn't gather any stats while it's running and doesn't update any of the sqlite_stat1-4 tables. Create Index is all you need to run for the index to be created, populated, and used. Analyze gives the query planner a more accurate view of how useful the index might be for whatever query it's planning.

I have often thought that a "create analyzed index" statement would be nice addition though to do the two at the same time.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Nelson, Erik - 2
Sent: Wednesday, December 20, 2017 10:21 AM
To: SQLite mailing list
Subject: [sqlite] create index implies analyze?

If I am finished making inserts into a table, does the act of creating an index also populate the index?  That is, do I need to separately run 'analyze'?

My impression was that there was no need to call analyze unless rows had been inserted after index creation.

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
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
Reply | Threaded
Open this post in threaded view
|

Re: create index implies analyze?

Simon Slavin-3


On 20 Dec 2017, at 5:19pm, David Raymond <[hidden email]> wrote:

> I have often thought that a "create analyzed index" statement would be nice addition though to do the two at the same time.

Perhaps not as useful as you might think, because most people create indexes while their tables are empty.  ANALYZE gathers information about the data in your table.  For instance, the difference between an 'inStock' column which has just two values 'yes' and 'no', and a 'surname' column in which almost every value is different.  To do this, it needs to be run after the table has data.

Simon.
_______________________________________________
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: create index implies analyze?

R Smith-2
On 2017/12/20 7:27 PM, Simon Slavin wrote:
> On 20 Dec 2017, at 5:19pm, David Raymond <[hidden email]> wrote:
>
>> I have often thought that a "create analyzed index" statement would be nice addition though to do the two at the same time.
> Perhaps not as useful as you might think, because most people create indexes while their tables are empty. //...

Quite right - but...

With really big in-use tables, I often find myself dropping indexes,
doing bulk deletes/inserts then recreating the Indexes and doing an
Analyze - Not to mention the fact that we often teach others to do this
(as is the case in a recent thread), so since CREATE INDEX must walk the
table already, so perhaps there does exist a real niche for CREATE
ANALYZED INDEX ....;

That said, I've never known Analyze to consume significant time (but
that might be due to many things), so not sure the savings will be
significant for the general case.


_______________________________________________
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: create index implies analyze?

Richard Hipp-3
On 12/20/17, R Smith <[hidden email]> wrote:
> I've never known Analyze to consume significant time

ANALYZE runs in O(N) steps, where N is the number of rows in the
table.  It does a single scan through each index being analyzed, from
beginning to end.

CREATE INDEX, on the other hand, requires O(NlogN) steps.

ANALYZE is normally fast, but can take significant time on a large
(terabyte-sized) database.  Earlier this year, we worked on an
experimental utility program that does a reasonable approximation of
ANALYZE in constant time.  See the "faststat1" utility on the
"est_count_pragma" branch:
https://www.sqlite.org/src/timeline?r=est_count_pragma

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