performance impact of index creation order

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

performance impact of index creation order

Nelson, Erik - 2
Assuming that table 'data' is completely constructed, does index creation order have any performance ramifications?

For example, would it be reasonable to assume that the order of these two statements has no performance impact?  (all fields are integer in this case)

create unique index data_index_1 on path_data(id, path, date)
create index data_index on data(path, date)


----------------------------------------------------------------------
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] performance impact of index creation order

Hick Gunter
You are not showing the definition of data. Some table constraints (e.g. UNIQUE or PRIMARY KEY) are implemented via an index, so creating a superflous index that duplicates an automatically created index only serves to waste space in the file and time to maintain.

It is possible that creating the unique index first may speed up creation of the second index containing subset of the fields similar to when a covering index is used for a query. You will just have to try this out for yourself. You may find the .timer command of the SQLite shell useful. Be careful to exclude the effects of caching, as these may swamp any differences in speed.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nelson, Erik - 2
Gesendet: Mittwoch, 20. Dezember 2017 16:38
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] performance impact of index creation order

Assuming that table 'data' is completely constructed, does index creation order have any performance ramifications?

For example, would it be reasonable to assume that the order of these two statements has no performance impact?  (all fields are integer in this case)

create unique index data_index_1 on path_data(id, path, date) create index data_index on data(path, date)


----------------------------------------------------------------------
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: performance impact of index creation order

Simon Slavin-3
In reply to this post by Nelson, Erik - 2


On 20 Dec 2017, at 3:38pm, Nelson, Erik - 2 <[hidden email]> wrote:

> Assuming that table 'data' is completely constructed, does index creation order have any performance ramifications?
>
> For example, would it be reasonable to assume that the order of these two statements has no performance impact?  (all fields are integer in this case)
>
> create unique index data_index_1 on path_data(id, path, date)
> create index data_index on data(path, date)

The 'id' column is unique.  And as id is also the primary key of the table (assuming you’re doing things the normal way) there’s already a UNIQUE index made on it.  So there’s no point in creating the first index.

As a general rule, once you have created all your indexes, the order in which indexes were created makes no difference to timing for future operations.

Once you have created all your indexes, and populated your tables with plausible data, run ANALYZE.  Although you can do this with empty tables, it can gather far more useful information once it sees the sort of data you put in your tables.

Once you’re run ANALYZE once there’s no need to run it again unless you change your schema or radically change the kind of data stored in you tables.

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: [EXTERNAL] performance impact of index creation order

Nelson, Erik - 2
In reply to this post by Hick Gunter
Hick Gunter Sent: Wednesday, December 20, 2017 10:51 AM

>You are not showing the definition of data. Some table constraints (e.g. UNIQUE or PRIMARY KEY) are implemented via an index, so creating a superflous index that duplicates an automatically created index only serves to waste space in the file and time to maintain.


The table definition doesn't have any constraints/primary keys, simply (note that 'id' is not unique)

Create table path_data(id int, path int, month int)

>It is possible that creating the unique index first may speed up creation of the second index containing subset of the fields similar to when a covering index is used for a query. You will just have to try this out for yourself. You may find the .timer command of the SQLite shell useful. Be careful to exclude the effects of caching, as these may swamp any differences in speed.

Right, I was hoping for general comments on the index creation order, hoping not to pessimize the application.  It's not a big thing either way... the database and temp store is in memory with journal mode off if that makes a difference, and it takes a day or more to populate the table so this would not be a first order performance issue in any event.  I was just curious if anyone had insight into the relative merits of varying the index creation order.


-----Ursprüngliche Nachricht-----

Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nelson, Erik - 2

Gesendet: Mittwoch, 20. Dezember 2017 16:38

An: SQLite mailing list <[hidden email]>

Betreff: [EXTERNAL] [sqlite] performance impact of index creation order

Assuming that table 'data' is completely constructed, does index creation order have any performance ramifications?

For example, would it be reasonable to assume that the order of these two statements has no performance impact?  (all fields are integer in this case)

create unique index data_index_1 on path_data(id, path, date)

create index data_index on data(path, date)


----------------------------------------------------------------------
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] performance impact of index creation order

David Raymond
Order of those two create index statements isn't going to matter, neither is going to speed up the execution of the other.

If it was a database in WAL mode with other people reading from the database while you're making the indexes, then start with the one that would be used more, as people will be able to use it while you're making the second one. But otherwise nope, order won't matter.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Nelson, Erik - 2
Sent: Wednesday, December 20, 2017 11:07 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] performance impact of index creation order

Hick Gunter Sent: Wednesday, December 20, 2017 10:51 AM

>You are not showing the definition of data. Some table constraints (e.g. UNIQUE or PRIMARY KEY) are implemented via an index, so creating a superflous index that duplicates an automatically created index only serves to waste space in the file and time to maintain.


The table definition doesn't have any constraints/primary keys, simply (note that 'id' is not unique)

Create table path_data(id int, path int, month int)

>It is possible that creating the unique index first may speed up creation of the second index containing subset of the fields similar to when a covering index is used for a query. You will just have to try this out for yourself. You may find the .timer command of the SQLite shell useful. Be careful to exclude the effects of caching, as these may swamp any differences in speed.

Right, I was hoping for general comments on the index creation order, hoping not to pessimize the application.  It's not a big thing either way... the database and temp store is in memory with journal mode off if that makes a difference, and it takes a day or more to populate the table so this would not be a first order performance issue in any event.  I was just curious if anyone had insight into the relative merits of varying the index creation order.


-----Ursprüngliche Nachricht-----

Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nelson, Erik - 2

Gesendet: Mittwoch, 20. Dezember 2017 16:38

An: SQLite mailing list <[hidden email]>

Betreff: [EXTERNAL] [sqlite] performance impact of index creation order

Assuming that table 'data' is completely constructed, does index creation order have any performance ramifications?

For example, would it be reasonable to assume that the order of these two statements has no performance impact?  (all fields are integer in this case)

create unique index data_index_1 on path_data(id, path, date)

create index data_index on data(path, date)


----------------------------------------------------------------------
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